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

First off I would like to thank all of the sponsors, volunteers, and coordinators of SQL Saturday Jacksonville. It was very well run and overall a great experience. I also want to give huge thanks to all of the attendees, without you coming on a beautiful Saturday, we would have had an event of just speakers. That would have proved to be interesting. To all the new folks I me,t you all are awesome and I look forward to meeting up again in the future.

I was excited to share my presentation More than Just a basic Database Backup and Recovery with the group. Overall the presentation went well. I thought my cadence was good and you the attendees were attentive when I was speaking. But, yes there is a but for this one.  As I moved forward with the presentation I realized my timings were off. I moved into my demos and the first couple went well. However 50% of the way into my demos I noticed that I had not reset my environment. This did throw some kinks into my presentation, as well as, my demos. I was able to cover some really cool stuff; however, I did not get to the real nuts and bolts of the recoveries. Part of my goal was to show you how to do partial and piecemeal recoveries. Unfortunately time got away from us on Saturday morning. I had done this presentation prior to this but I did not tweak and account for the 60 minutes we had on Saturday.

I received good reviews on the topic and the material that was covered. I also received excellent feedback that provided insight into how the session could have gone better. I have taken this feedback to heart and will be tweaking this presentation for future events.

Provided is a link to a Webinar in which I cover all the topics of the slide deck.

http://pragmaticworks.com/Training/Details/More-than-Just-a-Basic-Database-Backup-and-Recovery

Take a ways from this session:

  • Add to my checklist to reset my demo environment before each session (I have already put these measures into place. Not sure what it wasn’t there from the beginning. )
  • I need to watch my timings. I like to talk and sometimes I can’t get out of my own way…

Follow-up

  • There was a great question with regard to differential backups and how the differential change map behaves when SQL Server decides it would be more beneficial to do a full backup. I plan on doing a blog post on this to show what happens.
  • Prep this session for SQL Saturday Atlanta

As always thank you for reading and hanging out with me and if you have any questions please reach out to me.

Your @DBABulldog

Dan