Using Perfmon Counters to Gather Performance Data

You’ve probably heard the saying that knowledge is power. Indeed it’s very hard to make good decisions about complex problems without data. Data gives you the insight to know what’s going on so that you can make quality decisions that will actually help solve the problem.

Recently, I’ve been dealing with some performance issues in a Sql Server 2008 R2 database. Microsoft has built counters into Windows, Sql Server and ASP.NET that allow you to gather metrics about specific operations. Many of these counters have industry standard thresholds that you can compare your actual numbers with to help identify when there is a problem.

So how do you get this data? If you know C# or VB.Net, it’s super easy. Here’s a simple, rough example:

class Perfmon
   private static PerformanceCounter _percentProcessorTime
     = new PerformanceCounter("Processor", "% Processor Time", "_Total");

   static void Main(string[] args)
       if (PerformanceCounterCategory.CounterExists("% Processor Time", "Processor"))
           var actual_percentProcessorTime = _percentProcessorTime.NextValue();
           //Do what you want with the counter data.

Pretty easy, right? But how do you know which performance counters to gather data from? Fortunately, there are a number of good resources out on the interwebs. One of the best resources I’ve found for gathering Sql Server specific performance data is a poster from Quest (now part of Dell; makers of TOAD software). You can download the poster here (PDF). This poster gives a number of Perfmon counters that deal with the physical server and Sql Server. As it lists each counter, it provides a description and a threshold, if applicable. The thresholds are really helpful when starting to gather this data and make sense of it.

You can also gather counter data via the Perfmon.exe application that comes baked into Windows. This tool allows you to log the data to a number of file formats (CSV, Binary) and also log directly to a Sql Server table if you wish). Check out this article and video from Brent Ozar for more information there. The Perfmon tool will also graph the data in real time, much like the Windows Task Manager does.

A third option is available if you wish to only gather Sql Server metrics (including things like recent expensive queries). This link, gives you the actual queries that you can run (providing you have login credentials with the appropriate rights). You could even leverage SSRS (Sql Server Reporting Server) to build a report against the data and create a real-time dashboard.

I opted to retrieve the counter data from a C# console applications for two reasons: first, C# is what I’m the most comfortable with. But secondly, I wanted to be able to insert checks based on the recommended thresholds and provide real-time alerts via email to the team.

If you wish to log the data via the Perfmon.exe tool and then analyze the data after the fact, I recommend that you check out the Performance Analysis of Logs (PAL) tool. It’s free! It provides you with a number of threshold files that you can use to compare the gathered data against. You can export the threshold files to import them into the Perfmon.exe application so you don’t have to manually set up all of the recommended counters. PAL will built a list of times where thresholds were exceeded or were close to being exceeded. But that’s not all, it will plot the data on graphs. I highly recommend this tool!

Happy Data Crunching!

Share this Story:
  • facebook
  • twitter
  • gplus

About Jeremy Foltz

Leave a comment