Freigeben über

What should we do when the sql server performance counters are missing

  • Artikel

Performance Monitor Counters has always been the "Bread and Butter" for System Engineers and Administrators alike. With each new version of Windows, Microsoft has always improved on and provided a "better than ever" performance monitors. With the performance monitor Objects and counters, we get a detailed view and we know which part to tune.

As a SQL Server Support engineer, we have seen quite a few issues where we are not able to see the Performance Monitor counters for the SQL Server. In this Blog post I would like to share few easy steps using which we might get the Performance monitor back for SQL Server.

Cause 1

SQL Server Performance Objects and counters are installed automatically as part of the installation. Very often we do not see the SQL Performance Objects and counters in Performance Monitor because during the installation itself, SQL installation failed to install the Performance objects and counters.

In the SQL Setup logs, ideally we should see setup failures mentioning Performance Objects. We can also search for Error: 3409.

If that is the case, then we can query the System views using the DMV to confirm that…

select * from sys.dm_os_performance_counters

(Query SysperfInfo if you are using SQL 2000)

The result set should be empty. If it’s not, then it means that part of the installation was successful, but the OS components for the Performance objects could not be loaded.

Cause 2

These days where we are moving to X64 environment, we need to ensure that we are using the right tools to look at the data. Do I mean that we might not be using the "Correct" performance monitor? Yes, that is exactly what I meant. We might land into these scenarios if we have x64 Windows and X86 SQL.

In this case, for looking into X86 instances of SQL, we need to use the 32 bit equivalent of Performance Monitor. We can find that in C:\Windows\SysWOW64 or open that from RUN Command by typing the following.

perfmon /32

Now how we do we load the Performance Objects and counters back (in case they are not present. I guess that’s not present and that’s what led you to this post in the first place.)

1) First unload the counters, to remove any remnants. We use the unlodctr component(shipped with Windows) to do that.

We need to run the following from the Run command.

unlodctr mssql$<instance name>

--> If we are dealing with a default instance, then we need to run

unlodctr mssqlserver

If it is not loaded, we will get a well self explanatory message.

2) Then we need to identify the proper INI file using which we would load the counters. The actual path for the counter can be located in the following location

HKLM\SYSTEM\Services\Currentcontrolset\Services\MSSQL$<instance name>\Performance\

The Key in this Location would be PerfIniFile

(For default instance HKLM\SYSTEM\Services\Currentcontrolset\Services\MSSQLServer\Performance\PerfIniFile)

For Named Instance the file name would be in the format perf-<instancename>sqlctr.ini

For Default instance it would be in the form of sqlctr.ini

The file is present in the Binn Directory for the respective instance. Using the Windows Desktop Search facility we might save ourselves some time, if we are not sure where to find the specific file.

3) Now use the lodctr utility (from RUN Command) to load the Objects and counters using the identified INI file from above step. We need to specify the complete path for the INI file as the only option for lodctr.

lodctr <complete path for the ini file identified from the above steps)

On a clustered instance the unlodctr and lodctr works the same way, its only that we might have to failover the SQL to each node and run the same steps on each node.

Cause 3

Sometimes we might see these issues because of the permissions on the registry skewed. So we should check if the permissions for required accounts are there.

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 Creater 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

DEMO

Now I would try the above mentioned steps in unloading and reloading Performance objects on one of my installed instances.

I have a SQL called GMACH2K8\YUKONENT1. The instance name is YUKONENT1.

******Step 1******

Suddenly one fine day, I realize that the Performance Counters for this instance is missing. I first see if the counters are available within SQL or not.

clip_image002

A query to sys.dm_os_performance_counters fetched no results. I have to get this thing going as soon as possible, so I have little scope for finding the Root cause.

******Step 2******

So now I go ahead and try to find the name of the INI file using which I can load the Performance counters.

clip_image004

So you can see, I see the name is in fact in the form of perf-<instance name>sqlctr.ini. In my case the Instance name is YUKONENT1 and the name of the INI file is perf-YUKONENT1sqlctr.ini.

The location of the registry key is Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$YUKONENT1\Performance\PerfIniFile

******Step 3******

Then I go ahead and first try to unload the counters

clip_image006

ð I see the above message.

I found the location of perf-YUKONENT1sqlctr.ini to be C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Binn in my case.

******Step 4******

So next I go ahead and load the counters.

clip_image008

****** Step 5******

Next Step, is of course restarting the SQL Server J.

After restarting the SQL, I query Sys.dm_os_performance_counters and I see the following…

clip_image010

Now let’s time to look into Performance Counter.

clip_image012

The issue on my machine occurred when I was on Windows 2008 X64. On Windows 2003 X64, you would have to open 32 bit Performance Monitor to look at the Performance Objects and counters for 32 bit SQL.

DISCLAIMER: This blog post talks about some of the most commonly faced scenarios and is not intended to be the extensive list of all possible causes and their respective fixes/workaround.

Gourav Das
SE, Microsoft Sql Server.

Reviewed By

Amit Banerjee
Technical Lead, Microsoft Sql Server.

Comments

  • Anonymous
    March 17, 2009
    PingBack from http://blog.a-foton.ru/index.php/2009/03/18/what-should-we-do-when-the-sql-server-performance-counters-are-missing/

  • Anonymous
    October 31, 2012
    Great Post Parikshit. I did it long time back and almost forgot the steps to reload these - thanks this post saved me. :)

  • Anonymous
    November 07, 2012
    Thanks it helped me aswell

  • Anonymous
    March 13, 2014
    Hi Team, In my case the file perf-<instancename>sqlctr.ini. is missing from Bin directory in passive node, hence I can't able to reload performance counter registry settings using lodctr, I found the same file in another (active) node in BIN directory and I am not sure how do I restore back the perf-<instancename>sqlctr.ini. file on Bin directory of MSSQL Server installation on passive node. Then I can repair it using lodctr, Could you please anybody can assist on this.

  • Anonymous
    December 02, 2014
    Wonderful information which fixes my issue.

  • Anonymous
    February 15, 2017
    Still useful. I had to look at the image to see the registry location. The text had an extra "services" subfolder.