Zero Rows return on querying DMV sys.dm_os_performance_counters

I have a user on SQL Server 2014 Standard Edition (RTM) with SYSADMIN role with Server View State Permissions but when I execute DMV sys.dm_os_performance_counters it returns no record.

Any idea what is wrong with permissions?

Output of @@Version:


Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation Standard Edition (64-bit) on
Windows NT 6.3 (Build 9600: ) (Hypervisor)

If you're sure the user in question has View Server State (and it looks like in your screenshot he does).

Then there are a number of reasons previously put into an msdn blog. Ranging from:

• Performance Objects and counters set-up during the SQL Server installation failed.

• A mixture of 64 and 32 bit platforms.

• Registry permissions have been skewed

To resolve this we can use the same steps outlined in the guidelines for reinstalling the performance counters in a different stack-exchange post:

Using an elevated administrator command prompt perform the following steps.

• Change the path to the BINN directory of the SQL Server instance you desire to correct.

(Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn)

• Execute unlodctr >

For example: unlodctr MSSQL$SQL2008 or SQLAgent$SQL2008 ...

• Execute lodctr /T:>

For example: perf-MSSQL$SQL2008sqlctr.ini or perf-SQLAgent$SQL2008sqlagtctr.ini for SQLAgent. The /T is important to load the SQL Server performance counter provider as a trusted provider.

• Cycle the remote registry service:

net stop "Remote Registry" then net start "Remote Registry"

• Force a WMI synchronization using winmgmt /resyncperfctr ">"

where PID is the process id of the WinPriv.exe (you can get this from Task Manager)

The following may also be required:

-
Make sure that the correct security permissions have been granted to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009

registry key:

• Grant Full Control permissions to the Creator Owner account.

• Grant Full Control permissions to the Administrators account.

• Grant Read permissions to the SQL Administrators on that box/Node.

• Grant Full Control permissions to the System account

StackExchange Database Administrators Q#112371, answer score: 4