SQL Performance Counters Disabled – Registry Setting I Forgot

Posted: May 12, 2016 in Uncategorized

Caution
What I am showing impacts your registry. Do all the precautionary steps to backup and ensure that if something bad happens when making this change you can get back to a stable state.

About 6 or 7 years ago, I had an issue where my SQL Server performance counters were not available when looking within performance monitor. I went thru the steps of unloading and reloading the counters according to Microsoft documentation and by looking at many of the blog posts out there. The performance counters still would not show.

My next step was to dig into the registry and look at the performance counters entry for SQL Server.

We can drill into the provided registry path by running regedit.exe:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance
*If you have a named instance you will need to choose it in place of MSSQLSERVER*

I found a curious registry value; one I had not seen in the past, “Disable Performance Counters”. Just recently I ran across this little gem again. However, I forgot about it until I started digging further. So this time I decided to blog about it in order to help you, the audience, and me to not forgot about this in the future and save time trouble shooting the issue.

In the provided screenshot I highlighted the registry setting, and you can see the value is set to 1. Having the value set to 1 indicates that our SQL Server performance counters are disabled, and we will not be able to choose them within performance monitor.

Image_registry_prechange

To show this I have provided the following screenshot. Within the screenshot we can clearly see that the SQL Server performance counters are not available to us.

Perfmon_prechange

In order for us to see the expected performance counters we need to modify the registry value to 0. You can do this by double clicking on the “Disable Performance Counters” registry value. Enter 0 into the window and it will convert it to the Hexadecimal equivalent. After you make the change, your registry setting should appear as it does in the provided screenshot below.

Image_registry_post_change

Open up performance monitor, scroll down, and you should see your SQL Server performance counters. Happy monitoring!

Perfmon_post_change

I would like to note when I did my demos, this change did not require a restart of my SQL Server instance or the server I was working on. I have read instances where a recycle of either was required. So please keep that in mind when making this change.
As always thank you for reading and hanging out with me. If you have any questions please reach out.
@DBABulldog
Dan

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s