Collect and store historical SQL Server performance counter data with DMVs

Problem

I’d like to capture and store specific SQL Server performance counters over time, but I don’t want to have to set up Performance Monitor to run on each of my SQL Servers – I have too many, and it would take too much hands-on-keyboard computer time to put that plan in play. Do you have any suggestions?

Solution

As a matter of fact I do have a process in mind that I use in my own organization. It builds upon some of the concepts I have written about over the years in various tips here at MSSQLTips.com. I am going to give you a bit of reading homework first since what I will be discussing here depends upon understanding of running scripts against multiple SQL Server instances at one time and familiarity with a specific Dynamic Management View: sys.dm_os_performance_counters.  This tip actually builds upon a framework I outlined in the last tip listed below, but I’ve improved upon the solution presented at that time by accomodating named instances and also providing the code for a SQL Agent Job that will allow for the automatic collection of results for trending.

Please take a look over the three tips I’ve previously published on the subjects of running the same command against multiple instances and on the sys.dm_os_performance_counters Dynamic Management View:

• Run SQL Command Against Multiple SQL Server Instances Without CMS
• SQL Server Performance Counter DMV: sys.dm_os_performance_counters
• Collecting SQL Server Performance Counter Data for Trending

This process involves three steps, each described separately below.

Step One

Create the Registered Server Group for the SQL Instances you intend to push this solution. I tend to do this step in three phases:

• Two test instances (one default instance and one named instance – you’ll see why shortly when we look at the results that are returned for queries against sys.dm_os_performance_counters for named instances.)
• A group of remaining non-production SQL instances since I intend to push out the solution and monitor its behavior for a couple days before I push to production servers.
• The production instances of SQL Server that I intend to run this process for monitoring.

It should be noted that since we’re dealing with Dynamic Management Views that this solution will only work for SQL Server 2005 and newer. Only for the purposes of this article have I created the MSSQLTips registered servers group.  It contains two instances: the default instance and the MAPS named instance.

Before proceeding I’d like to show you quickly what to expect when you query sys.dm_os_performance_counters.  If you read the tip I provided above you’ll have a good understanding of what to expect – for the default instance.  However, the results are different for named instances when it comes to the [object_name] column values.  Let’s take a look at the results of a simple query against this DMV for both a default and named instance and you’ll see what I mean, furthermore it will lead you to understand some of the dynamic T/SQL I employ in the next step when I populate the list of counters I intend to persist.

When run against the default instance the results appear as such:

When run against a named instance you’ll see the results differ when it comes to the object_name column:

You’ll see in the next step how I account for the fact that the instance name is integrated into the object_name value. 

Step Two

I utilize a dedicated database on all my SQL Servers to host tables, views, stored procedures and functions to run in a uniform fashion against all my SQL Server databases and instances. 

Here at MSSQLTips I’ve always referred to it as iDBA (named changed to protect the innocent DBA.)  So, in my iDBA database (or whatever name you prefer on your SQL Servers) I create the necessary objects to support the data collection process from sys.dm_os_performance_counters:

• MetaBOT schema
• MetaBOT.dm_os_performance_counters table – hosts the collected results from sys.dm_os_performance_counters
• MetaBOT.watched_counters table – hosts the counters that I intend to collect

I then populate the MetaBOT.watched_counters table with values and finally create a stored procedure that I can call from inside a scheduled SQL Server Agent job to populate the MetaBOT.dm_os_performance_counters table on a consistent basis.  This is where I account for the named instance’s impact on the object_name column by building the value dynamically based upon the core object name and the instance name as derived from use of the @@SERVICENAME constant:

You may note that I track quite a few counters here – I figure if I go through the effort of setting this up then I may as well capture too much than not enough.  Depending upon your environment and the number of databases you’re hosting you may wish to persist fewer counters.  Some of these counters exist in a 1:1 relationship to your database count.  I did calculations based upon my most-populous instance and determined that each row of data persisted averaged 232 bytes; from there I then looked at how frequently I ran the associated collection job impacted the amount of data I was storing.  Running the collection process every 10 minutes was estimated to yeild 1.5 gb/month.  Increasing the frequency to every two minutes meant increasing the storage required to 8 gb/month – and this was for a single instance.

Now it’s time to create the stored procedure that you’ll call from inside that SQL Server Agent job.  It is a simple INSERT statement from sys.dm_os_performance_counters filtered through an INNER JOIN against the MetaBOT.watched_counters table we created and populated in the last step.  I didn’t bother with creating any indexes in that table as it is so small that the optimizer would choose to scan the table instead of using the index.  The maintance overhead of an unused index is unwarranted.

Step Three

The final step involves creating the actual job to run on each instance for collection of counter values.

What I find easiset to do when coding SQL Agent Job creation is to build the job in the SQL Server Mangement Studio GUI and then script it out.  That is what I did here.  There is one change you need to make when doing this however – you must either remove or comment-out the line of code that presents the job_id GUID for output.  It’s not needed and will cause the script to fail across all but the initial instance when run.  There is a single variable in this code that allows you to set how frequently the job runs (@RunEveryXMinutes); set the value equal to the number of minutes you want to expire between job runs.

Once activated it’s just the matter of querying the persisted values in the MetaBOT.dm_os_performance_counters table.  I also recommend that you set up a purge command, this can be done as part of the MetaBOT.usp_collect_perfmon_counters stored procedure as a DELETE from the MetaBOT.dm_os_performance_counters table where the date_stamp column is less than whatever threshold you want to set.  Depending upon your needs I would also recommend creating an index on that column if your performance reviews warrant doing so.

In the next tip of this series I’ll examine different ways to disect this data and use it for analysis for such things as consolidation planning or reviewing for good people doing bad things in your databases.  (Think CURSORS! folks!)

Next Steps

• Stay tuned for another tip in this series in which I examine how I use this information to work towards a consolidation plan.
• Previous tips in this series are available here:
  • SQL Server Performance Counter DMV: sys.dm_os_performance_counters
  • Collecting SQL Server Performance Counter Data for Trending
• Additional tips on Retaining Historical Index Usage Stats from the Dynamic Management Views are available at MSSQLTips.
  • Part One
  • Part Two
  • Part Three

Tim Ford

Tim Ford is a Senior Database Administrator with MindBody in San Luis Obispo, California and is in the process of relocating west to the Pacific Northwest from Michigan. Since 2010 he’s produced Microsoft Data Platform training events branded as SQL Cruise from Alaska to the Caribbean and the Mediterranean at Tech Outbound, an events company specializing in technical training in unconventional locations. His SQL Cruise events take place on cruise ships in the Caribbean, Alaska, and the Mediterranean. Tim also is the Executive VP of Marketing for PASS, the global association for Microsoft data professionals. He also is a contributing author for itprotoday. Tim loves helping people find their true potential through education and building networks between Thought Leaders in various fields and those who are just starting on their careers or struggling to find their footing in established careers. If you’re looking for this sort of experience then check out the next SQL Cruise event taking place this August in Seattle.

• MSSQLTips Awards: Acheiver (75+ tips) – 2010