I’ve written in the past about the need to create a baseline of your SQL Server’s performance and how to do that. The list of Perfmon counters I used was from a list that Brent Ozar recommended in one of this videos. I saw something last week that made me add a couple.
Paul Randal and Kimberly Tripp and the rest of the gang over at SQLSkills.com have a free club you can join called SQL Insiders. Joining will get you a bi-weekly newsletter with cool info from Paul and a video about some SQL Server-related topic. I highly recommend signing up. Plus, you get a free sticker when you do! (And really, who doesn’t want a free sticker?)
The last newsletter featured a short video from Jonathan Kehayias about monitoring virtualized SQL Servers. One of the items he showed was a handful of Perfmon counters for VMs. I’ve got a lot of virtualized SQL Servers and the plan here is to move more in that direction, so I felt this was something I needed to monitor and went about adding the new counters to my standard counter collection process.
Only when I tried, Typeperf gave me a “Error: no valid counters” error. I knew from past experience that this is pretty much Typeperf’s generic “something’s wrong” error, so I had to do a little digging. First, I connected to the VM machine I was interested in and verified that I could see the counters via Perfmon locally. I could. I then connected to my collection machine and tried to look at the counters from a remote computer. No luck. I could see all the other counters, so I knew I had permissions set up correctly. All the non-VM counters were being collected without problem.
Hmm.. My first thought was that I mis-typed the name of the counter. I double-checked that, but the error persisted. I spent the better part of a couple hours trying to figure out what was wrong. At one point, I thought I was able to see the counters on the remote machine using Perfmon, but not Typeperf – which would be very strange indeed. But I was unable to duplicate that the following day, so I must have been mistaken.
A bit of googling finally pointed me to the solution: Restart the Remote Registry Service on the host machine. Once I did this, the VM counters showed up and Typeperf was able to see them.
I won’t steal Jonathan’s thunder by going in to details about what counters to watch and why. Join SQL Insiders and you can download the video yourself. The video is only about 15 minutes long and joining is free. You don’t get spammed and you get lots of good information. I don’t see any downside to joining.