SQL Performance Counters Disabled – Registry Setting I Forgot | DBABullDog

Источник: SQL Performance Counters Disabled – Registry Setting I Forgot | DBABullDog

Image_registry_prechange

Perfmon_prechange

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.

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.

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.

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *