11

I'm running SQL Server 2008 R2 Developer on Windows 7 x64, as the default instance. For some reason, SQL Server's performance counters seem to have disappeared. SELECT * FROM sys.dm_os_performance_counters returns zero rows.

I tried running lodctr /T:perf-MSSQLSERVERsqlctr.ini. Although it completed without errors, it didn't fix anything, even after restarting the Remote Registry service. In fact, the counters are now missing from perfmon. unlodctr MSSQLSERVER still says that the counters are not installed, but lodctr /Q:MSSQLSERVER says they're present and enabled. Regular Windows and .NET counters are working fine.

In the Windows Error log, I'm seeing Error 8317:

Cannot query value 'First Counter' associated with registry key 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance'. SQL Server performance counters are disabled.

I suspect the problem may have been triggered by an installation of SQL 2012 RC0 that failed. Any suggestions for how to recover, short of reinstalling SQL Server 2008 R2?

Improve this question
2
  • You mean "Visual Studio 11" (not "2011"?)
    – gbn
    Feb 23, 2012 at 7:58
  • Yes, VS 11. The problem with the question on serverfault is that none of the posted answers resolved the issue. I'm hoping DBA-oriented users might be able to provide more useful help.
    – RickNZ
    Feb 23, 2012 at 8:11

5 Answers 5

Reset to default
4

Have a try with lodctr /R, it will rebuild all performance counters from informations stored in registry.

Improve this answer
1
  • In my case there were more counters missing, not only SQL Server's. lodctr /R did the trick and restored them all. Thank you very much for the trick.
    – Endrju
    Sep 13, 2013 at 18:44
4

Read through this:

Link

The exact instructions are not 100% correct. You need to do this, assuming you are using the default instance:

unlodctr MSSQLSERVER
lodctr perf-MSSQLSERVERsqlperf.ini
lodctr /T:MSSQLSERVER

Then reboot.

Improve this answer
1
  • I found that symbolfile path in my perf-MSSQLSERVERsqlctr.ini was incorrect. I fixed it, executed lodctr /T:perf-MSSQLSERVERsqlctr.ini, lodctr perf-MSSQLSERVERsqlctr.ini and restarted SQL Server.
    – Der_Meister
    May 2, 2015 at 13:44
2

It looks like the Performance Counters for SQL Server are DISABLED or the registry entries for the performance counters are corrupted.

lodctr /E:MSSQLSERVER will reload the performance counter registry settings.

Improve this answer
2

Missing performance counters is recurring issue.

Some known solutions are listed here: Link

Improve this answer
0

If you're running a 32-bit version of SQL Server on a 64-bit installation of Windows you need to use the 32-bit version of Performance Monitor to view the counters as explained in this MSDN Blog. Do so using either of these methods:

  1. Run perfmon.exe from the %windir%\SysWOW64 folder
  2. Run the command mmc /32 perfmon.msc

Also, make sure you're running SQL Server with the latest Service Pack and Hotfixes. MSKB article 2708726 describes an issue where Performance Monitor .dll files don't get updated properly when applying updates to SQL Server 2008 R2 or SQL Server 2012.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.