Collect and store historical SQL Server performance counter data with DMVs

By:   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | > Dynamic Management Views and Functions


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:

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.

ssms registered servers

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.

SELECT [object_name], [counter_name], [instance_name], [cntr_value]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio';

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

buffer cache hit ratio

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

buffer cache hit ratio

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
USE [iDBA];
GO

CREATE SCHEMA MetaBOT AUTHORIZATION dbo;
GO

IF NOT EXISTS (SELECT name FROM iDBA.sys.[tables] T WHERE name = 'dm_os_performance_counters')
CREATE TABLE [MetaBOT].[dm_os_performance_counters]
(
[object_name] NVARCHAR(128),
[counter_name] NVARCHAR(128),
[instance_name] NVARCHAR(128),
[cntr_value] bigint,
[date_stamp] DATETIME
);

IF NOT EXISTS (SELECT name FROM iDBA.sys.[tables] T WHERE name = 'watched_counters')
CREATE TABLE [iDBA].[MetaBOT].[watched_counters]
(
[object_name] NVARCHAR(128),
[counter_name] NVARCHAR(128),
[active] bit
);

GO

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:

--+-- Now populate the watched counters table based upon instance properties
DECLARE @NamedInstance bit
DECLARE @ObjectNamePrefix VARCHAR(50)
SELECT @NamedInstance = 1

IF @@SERVICENAME = 'MSSQLSERVER'
BEGIN
SELECT @NamedInstance = 0  --This is the default instance

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Buffer cache hit ratio', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Buffer cache hit ratio base', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Database pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Free pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page life expectancy', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page lookups/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page reads/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page writes/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Reserved pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Stolen pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Target pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Total pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Cursor Manager by Type', 'Active cursors', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Active Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Data File(s) Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Log File(s) Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Log File(s) Used Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Percent Log Used', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Transactions/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Exec Statistics', 'DTC calls', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Exec Statistics', 'OLEDB calls', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_namme], [counter_name], [active])
VALUES ('SQLServer:General Statistics', 'Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:General Statistics', 'User Connections', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Latches', 'Latch Waits/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Latches', 'Total Latch Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Locks', 'Average Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Locks', 'Lock Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Memory Manager', 'Target Server Memory (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Memory Manager', 'Total Server Memory (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Plan Cache', 'Cache Hit Ratio', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Plan Cache', 'Cache Hit Ratio Base', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Plan Cache', 'Cache Pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:SQL Statistics', 'Batch Requests/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Free Space in tempdb (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Longest Transaction Running Time', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'NonSnapshot Version Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Snapshot Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Update Snapshot Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Version Store Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Lock waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Log buffer waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Log write waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Memory grant queue waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Network IO waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Non-Page latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Page IO latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Page latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Thread-safe memory objects waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Transaction ownership waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Wait for the worker', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Workspace synchronization waits', 1);
END

ELSE
BEGIN
   --Account for named instance when adding object names to watch
   SELECT @ObjectNamePrefix = 'MSSQL$' + @@SERVICENAME
  
   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Buffer cache hit ratio', 1);
  
   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Buffer cache hit ratio base', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Database pages', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Free pages', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page life expectancy', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page lookups/sec', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page reads/sec', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page writes/sec', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Reserved pages', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Stolen pages', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Target pages', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Total pages', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Cursor Manager by Type', 'Active cursors', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Databases', 'Active Transactions', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Databases', 'Data File(s) Size (KB)', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Databases', 'Log File(s) Size (KB)', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Databases', 'Log File(s) Used Size (KB)', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Databases', 'Percent Log Used', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Databases', 'Transactions/sec', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Exec Statistics', 'DTC calls', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Exec Statistics', 'OLEDB calls', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':General Statistics', 'Transactions', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':General Statistics', 'User Connections', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Latches', 'Latch Waits/sec', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Latches', 'Total Latch Wait Time (ms)', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Locks', 'Average Wait Time (ms)', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Locks', 'Lock Wait Time (ms)', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Memory Manager', 'Target Server Memory (KB)', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Memory Manager', 'Total Server Memory (KB)', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Plan Cache', 'Cache Hit Ratio', 1);
  
   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Plan Cache', 'Cache Hit Ratio Base', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Plan Cache', 'Cache Pages', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':SQL Statistics', 'Batch Requests/sec', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Transactions', 'Free Space in tempdb (KB)', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Transactions', 'Longest Transaction Running Time', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Transactions', 'NonSnapshot Version Transactions', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Transactions', 'Snapshot Transactions', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Transactions', 'Transactions', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Transactions', 'Update Snapshot Transactions', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Transactions', 'Version Store Size (KB)', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Lock waits', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Log buffer waits', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Log write waits', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Memory grant queue waits', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Network IO waits', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Non-Page latch waits', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Page IO latch waits', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Page latch waits', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Thread-safe memory objects waits', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Transaction ownership waits', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Wait for the worker', 1);

   INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
   VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Workspace synchronization waits', 1);
END

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.

CREATE PROCEDURE MetaBOT.usp_collect_perfmon_counters AS
DECLARE @datestamp DATETIME
SELECT @datestamp = GETDATE()
INSERT INTO MetaBOT.[dm_os_performance_counters]
  (
        [object_name],
        [counter_name],
        [instance_name],
        [cntr_value],
        [date_stamp]
  )
  SELECT
      DOPC.[object_name],
      DOPC.[counter_name],
      DOPC.[instance_name],
      DOPC.[cntr_value],
  @datestamp
  FROM  sys.[dm_os_performance_counters] DOPC
      INNER JOIN iDBA.[MetaBOT].[watched_counters] WC
      ON [DOPC].[object_name] = [WC].[object_name]
    AND [DOPC].[counter_name] = [WC].[counter_name]
  ORDER BY [object_name], [counter_name];

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.

USE [msdb]
GO
      /****** Object:  Job [Metadata_Collect_SQL_Perfmon_Counters]    Script Date: 12/09/2010 12:14:38 ******/
  BEGIN TRANSACTION
  DECLARE @ReturnCode INT
  DECLARE @RunEveryXMinutes TINYINT
  SELECT @ReturnCode = 0
  SELECT @RunEveryXMinutes = 10
        /****** Object:  JobCategory [Tuning and Optimization]    Script Date: 12/09/2010 12:14:39 ******/
  IF NOT EXISTS (
    SELECT name
      FROM msdb.dbo.syscategories
      WHERE name=N'Tuning and Optimization'
        AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Tuning and Optimization'
    IF (@@ERROR <> 0
          OR @ReturnCode <> 0) GOTO QuitWithRollback
      END
  DECLARE @jobId BINARY(16)
  EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Metadata_Collect_SQL_Perfmon_Counters',
    @enabled=1,
    @notify_level_eventlog=0,
    @notify_level_email=0,
    @notify_level_netsend=0,
    @notify_level_page=0,
    @delete_level=0,
    @description=N'Poll and store output from sys.dm_os_performance_counters',
    @category_name=N'Tuning and Optimization',
    @owner_login_name=N'SPECTRUM-HEALTH\svcSQLNotify', @job_id = @jobId OUTPUT
  IF (@@ERROR <> 0
          OR @ReturnCode <> 0) GOTO QuitWithRollback
          /****** Object:  Step [Collect Metadata]    Script Date: 12/09/2010 12:14:39 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect Metadata',
    @step_id=1,
    @cmdexec_success_code=0,
    @on_success_action=1,
    @on_success_step_id=0,
    @on_fail_action=2,
    @on_fail_step_id=0,
    @retry_attempts=0,
    @retry_interval=0,
    @os_run_priority=0, @subsystem=N'TSQL',
    @command=N'EXEC MetaBOT.usp_collect_perfmon_counters;',
    @database_name=N'iDBA',
    @flags=0
  IF (@@ERROR <> 0
        OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
  IF (@@ERROR <> 0
        OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'dm_os_perfmon_counters Job',
    @enabled=1,
    @freq_type=4,
    @freq_interval=1,
    @freq_subday_type=4,
    @freq_subday_interval=@RunEveryXMinutes,
    @freq_relative_interval=0,
    @freq_recurrence_factor=0,
    @active_start_date=20101209,
    @active_end_date=99991231,
    @active_start_time=30,
    @active_end_time=235959
    -- ,@schedule_uid=N'519325e6-3114-453c-bfeb-6597300a45d2' <---Comment this line out of auto-generated script
  IF (@@ERROR <> 0
        OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
  IF (@@ERROR <> 0
        OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
        GOTO EndSave
        QuitWithRollback:
  IF (@@TRANCOUNT > 0)
    ROLLBACK TRANSACTION
        EndSave:
GO

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, April 19, 2012 - 3:59:10 AM - Neil Macehiter Back To Top (16997)

Great stuff: very helpful.

There is a minor bug in the second script: object_namme rather than object_name in one of the insert statements.

Tuesday, October 25, 2011 - 8:27:04 AM - Liam Dwan Back To Top (14917)

Hi.  Thanks for the article - really informative.  

I have a problem with some of the counters accessible via sys.dm_os_performance_counters - especially those with a cntr_type of 1073874176 (PERF_AVERAGE_BULK) and specifically 'SQLServer:Locks', 'Average Wait Time (ms)' which I hope you might be able to help me with please?

There is a dearth of information on these counters. I have been able to figure out that to use them you need to find the equivalent "base" counter - e.g. "'Average Wait Time Base". In most cases it seems that you divide the "main" counter by the base. However for "Average Wait time", this does not give a sensible result.  For example on my production server, the current values for "Average Wait Time" are

object_name           counter_name               instance_name   cntr_value   cntr_type

MSSQL$SW1:Locks Average Wait Time (ms)  _Total                72315373 1073874176

MSSQL$SW1:Locks Average Wait Time Base  _Total        225          1073939712

which would give a value of 321,401 ms  for the average wait time - obvioulsy wrong.

Tuesday, July 12, 2011 - 4:33:06 AM - Darshna Back To Top (14152)

An excellent contribution. - thank you...!!!

Tuesday, December 21, 2010 - 7:07:11 AM - Ayyappan Back To Top (10475)

I would like to thank for you valuable effort.

Why don't we use Performance data warehouse that is in SQL Server 2008?














get free sql tips
agree to terms