Had an issue with a new Azure VM install of SQL Server, but have had this issue in other, older places as well. SQL Server's performance counters in dm_os_performance_counters and/or perfmon have gone missing. How to get them back?
You might see a small subset of all performance counters here in the DMV view. (There should be 1335 rows in this DMV for SQL Server 2014 RTM, 1245 for SQL 2012 SP2. More are added regularly with major releases.) In the example I saw this morning, only 51 rows were returned, and only for the XTP series of objects (XTP Transaction Log, XTP Cursors, etc).
Strangely, I had now found this problem on brand-new Azure VM's running SQL Server, specifically in the pre-built SQL 2014 Availability Group resource group, which can be deployed easily from the new Azure portal.
The issue can be resolved with a pair of PowerShell commands which re-register performance counter names and explanation text to the SQL Server service in the Windows registry.
In PowerShell, modify the below for your service.
- In the first line, change the driver name parameter to match the SQL Server instance name, MSSQLSERVER is the name for the default instance. To do the same for a specific instance, it would be MSSQL$InstanceName.
- In the second line, replace my path below with the path for your instance.
unlodctr MSSQLSERVER
lodctr "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini"
A restart of the affected service, in this case the SQL Server service, is necessary for the changes to take effect.
