Automatically Capturing SQL Server Performance Metrics

A few months back I wrote an article on monitoring SQL Server performance. In that article, I defined 10 counters you should watch and monitor when looking at your SQL Server performance. One of my readers asked how you might programmatically collect these counter values. This was a great question, so in this article I will answer that question and provide a method that you can use to collect my top 10 performance metrics automatically over time.

Before I jump into a solution, I must first say the solution I will be providing is only available if you are running on SQL Server 2005 or above. I hope by now most of you are running on versions of SQL Server that will support the solution I am about to show you. 

Developing a SQL Server Performance Monitoring System

Whenever you want to track something over time, it is important to determine a reasonable method to capture those metrics you want to track. One method of tracking metrics might be to write them down, in a journal. Then you can refer to those journal entries another time to compare the differences. This method is tedious and error prone. It is better to build a solution that automatically will keep track of any metrics you want to capture.

By automatically, I mean a solution that runs routinely and stores your metric in a database table. If you do this, you will be able to produce trend reports for your different counters values quickly and easily, as the one found in Figure 1. Graphical trend reports give you a visual perspective of how your performance utilization changes overtime. In figure 1, we can tell the server is lightly used from the number of concurrent user perspective. However, it also tells us the number of concurrent users climbs throughout the business day and drops off during the noon lunch hour and around quitting time (5 p.m.).

sql concurrent users

Figure 1: Graph of Top Number of Concurrent Users per Hour 8 am to 5 pm of a given day

How to Programmatically Capture SQL Server Performance Metrics

With the introduction of SQL Server 2005, Microsoft introduced Dynamic Management Views (DMVs). One of the DMVs they provided was sys.dm_os_performance_counters, which allows you to easily capture a number of different performance metrics. There are thousands of different performance metrics this DMV exposes. Best of all it just takes a little bit of T-SQL code to capture these performance metrics. 

All of the counters on my top 10 list can be obtained by using this DMV. But in order to extract the different metrics, you must first know how these metrics are exposed via this DMV. To view all the different counter values available using this DVM, run the following SELECT statement:

SELECT * FROM sys.dm_os_performance_counters 

If you run this code, you can see there are a number of different counters available.  The counter values from the DMV fall into three different types of counters, those that can be extracted using a single row, those that require two rows to determine the counter value and those where you have to take multiple samples of the counter valve in order to obtain the actual counter value. If you look at the cntr_type column in the output from the SELECT command above will see different numbers. These numbers help you identify the different counter types. Let me show you some code snippets of how to capture these three different types of counters, and then I will provide you a solution that will capture all 10 of my favorite performance counters.

Using the sys.dm_os_performance_counters

The first type of counter I will be showing you is how to capture the counter value for those counters that can be obtained by looking at a single row of the sys.dm_os_performance_counter output. The code below shows you how to collect one of those counters, known as page life expectancy:  

SELECT * FROM sys.dm_os_performance_counters

WHERE counter_name = ‘Page life expectancy’

  AND object_name like ‘%:Buffer Manager%’

By reviewing the output of this command, the column cntr_value can be used to identify the page life expectancy metric. You can extract the performance metric for any of the counters that have a cntr_type equal to 65792 that are exposed with this DVM the same way I did in the code snippet above. 

Some of the counters like, Buffer Cache hit ratio require you to collect the counter value from two different records and then perform some math to calculate the real performance metric. Here is an example of how to capture the Buffer Cache hit ratio by using two different records from the sys.dm_os_performance_counters DMV:

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]

FROM (SELECT * FROM sys.dm_os_performance_counters  

        WHERE counter_name = ‘Buffer cache hit ratio’

          AND object_name LIKE ‘%:Buffer Manager%’) a  

    CROSS JOIN

     (SELECT * FROM sys.dm_os_performance_counters  

        WHERE counter_name = ‘Buffer cache hit ratio base’

          and object_name LIKE ‘%:Buffer Manager%’) b 

Here you can see I captured the counter value for the ‘Buffer cache hit ratio’ and the ‘Buffer cache hit ratio base’ by using two different DMV records. I then used these values to calculate the real buffer cache hit ratio. 

Lastly, there are per second counter values. These performance counters are exposed using the sys.dm_os_performance_counters DMV, but the counter value displayed by the DMV shows the number of times that each counter event has occurred since SQL Server has started up. Therefore, in order to get the current actual per second value for a per second counter you need to capture the counter value twice between a known interval, and then calculate the actual per second value. Here is an example where I calculate the Batch Requests / Sec:

— get first sample

SELECT @value1=cntr_value, @time1 = getdate()

  FROM sys.dm_os_performance_counters

   WHERE counter_name = ‘Batch Requests/sec’

     AND object_name LIKE ‘%SQL Statistics%’;

— wait for 10 seconds

WAITFOR DELAY ’00:00:10′;

— get second sample

SELECT @value2=cntr_value, @time2 = getdate()

  FROM sys.dm_os_performance_counters

   WHERE counter_name = ‘Batch Requests/sec’

     AND object_name LIKE ‘%SQL Statistics%’;

— calculate page reads per second

SELECT (@value2 – @value1) / datediff(ss,@time1,@time2) [Batch Requests/sec];

In this example, you can see that I first captured the value of the ‘Batch Requests/sec’ counter. Then I waited 10 seconds. After waiting 10 seconds, I captured the second sample and then comparing the two samples and divided by the time difference between the two samples. Using this method, I was able to capture the actual batch requests per second. 

What I have shown you so far, is only the methods that can be used to obtain the counter values programmatically from the sys.dm_os_performance_counters DMV. What is needed is an automated process that will capture the performance counters, so performance metrics can be captured over time. By doing this you will be able to have a performance metrics over time and this will allow you to produce trend reports from those historical metrics.   

Automating the Capturing of Performance Metrics

If you want to produce nice trend reports from your performance metrics, you need to do two things. First, is to determine where to save the collected performance metric values over time. A SQL Server table is a good place to do this. Secondly, you need to build a method to capture your performance metrics every so often. I find building a script that captures my performance metrics and then insert records into a SQL Server table is a good method for capturing my performance metrics. Then I trigger the script via SQL Server agent to automate the collection process. Below I have provided you with the code to accomplish this.

The first step to building this process is to create a stored procedure that will output a single row containing all the performance metrics I want to capture. Using the techniques I described in the prior section, I created the following script that will create a stored procedures named ‘GetMetrics’ that will display my top 10 performance counters in a single row of output. Note that this script assumes the stored procedure will be created in a database name DBA, but any database will do. This stored procedure took the methods I described above to capture my performance metrics from the sys.dm_os_performance_counters DMV.  

USE DBA;

GO

CREATE PROC GetMetrics

AS

SET NOCOUNT ON;

 

— Variables for Counters

DECLARE @BatchRequestsPerSecond BIGINT;

DECLARE @CompilationsPerSecond BIGINT;

DECLARE @ReCompilationsPerSecond BIGINT;

DECLARE @LockWaitsPerSecond BIGINT;

DECLARE @PageSplitsPerSecond BIGINT;

DECLARE @CheckpointPagesPerSecond BIGINT;

 

— Variable for date

DECLARE @stat_date DATETIME;

 

— Table for First Sample

DECLARE @RatioStatsX TAbLE(

       [object_name] varchar(128)

      ,[counter_name] varchar(128)

      ,[instance_name] varchar(128)

      ,[cntr_value] bigint

      ,[cntr_type] int

      )

 

— Table for Second Sample

DECLARE @RatioStatsY TABLE(

       [object_name] VARCHAR(128)

      ,[counter_name] VARCHAR(128)

      ,[instance_name] VARCHAR(128)

      ,[cntr_value] BIGINT

      ,[cntr_type] INT

      );

 

— Capture stat time

SET @stat_date = getdate();

 

INSERT INTO @RatioStatsX (

     [object_name]

      ,[counter_name]

      ,[instance_name]

      ,[cntr_value]

      ,[cntr_type] )

      SELECT [object_name]

            ,[counter_name]

            ,[instance_name]

            ,[cntr_value]

            ,[cntr_type] FROM sys.dm_os_performance_counters;

 

— Capture each per second counter for first sampling

SELECT TOP 1 @BatchRequestsPerSecond = cntr_value

      FROM @RatioStatsX

    WHERE counter_name = ‘Batch Requests/sec’

      AND object_name LIKE ‘%SQL Statistics%’;

 

SELECT TOP 1 @CompilationsPerSecond = cntr_value

      FROM @RatioStatsX

    WHERE counter_name = ‘SQL Compilations/sec’

      AND object_name LIKE ‘%SQL Statistics%’;

 

SELECT TOP 1 @ReCompilationsPerSecond = cntr_value

      FROM @RatioStatsX

    WHERE counter_name = ‘SQL Re-Compilations/sec’

      AND object_name LIKE ‘%SQL Statistics%’;

 

SELECT TOP 1 @LockWaitsPerSecond = cntr_value

      FROM @RatioStatsX

    WHERE counter_name = ‘Lock Waits/sec’

      AND instance_name = ‘_Total’

      AND object_name LIKE ‘%Locks%’;

 

SELECT TOP 1 @PageSplitsPerSecond = cntr_value

      FROM @RatioStatsX

    WHERE counter_name = ‘Page Splits/sec’

      AND object_name LIKE ‘%Access Methods%’; 

 

SELECT TOP 1 @CheckpointPagesPerSecond = cntr_value

      FROM @RatioStatsX

      WHERE counter_name = ‘Checkpoint Pages/sec’

        AND object_name LIKE ‘%Buffer Manager%’;                                         

 

WAITFOR DELAY ’00:00:01′

 

— Table for second sample

INSERT INTO @RatioStatsY (

            [object_name]

            ,[counter_name]

            ,[instance_name]

            ,[cntr_value]

            ,[cntr_type] )

   SELECT [object_name]

            ,[counter_name]

            ,[instance_name]

            ,[cntr_value]

            ,[cntr_type] FROM sys.dm_os_performance_counters

 

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]

      ,c.cntr_value  AS [PageLifeExpectency]

      ,d.[BatchRequestsPerSecond]

      ,e.[CompilationsPerSecond]

      ,f.[ReCompilationsPerSecond]

      ,g.cntr_value AS [UserConnections]

      ,h.LockWaitsPerSecond 

      ,i.PageSplitsPerSecond

      ,j.cntr_value AS [ProcessesBlocked]

      ,k.CheckpointPagesPerSecond

      ,GETDATE() AS StatDate                                     

FROM (SELECT * FROM @RatioStatsY

               WHERE counter_name = ‘Buffer cache hit ratio’

               AND object_name LIKE ‘%Buffer Manager%’) a  

     CROSS JOIN  

      (SELECT * FROM @RatioStatsY

                WHERE counter_name = ‘Buffer cache hit ratio base’

                AND object_name LIKE ‘%Buffer Manager%’) b    

     CROSS JOIN

      (SELECT * FROM @RatioStatsY

                WHERE counter_name = ‘Page life expectancy ‘

                AND object_name LIKE ‘%Buffer Manager%’) c

     CROSS JOIN

     (SELECT (cntr_value – @BatchRequestsPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [BatchRequestsPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘Batch Requests/sec’

                AND object_name LIKE ‘%SQL Statistics%’) d   

     CROSS JOIN

     (SELECT (cntr_value – @CompilationsPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [CompilationsPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘SQL Compilations/sec’

                AND object_name LIKE ‘%SQL Statistics%’) e 

     CROSS JOIN

     (SELECT (cntr_value – @ReCompilationsPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [ReCompilationsPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘SQL Re-Compilations/sec’

                AND object_name LIKE ‘%SQL Statistics%’) f

     CROSS JOIN

     (SELECT * FROM @RatioStatsY

               WHERE counter_name = ‘User Connections’

               AND object_name LIKE ‘%General Statistics%’) g

     CROSS JOIN

     (SELECT (cntr_value – @LockWaitsPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [LockWaitsPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘Lock Waits/sec’

                AND instance_name = ‘_Total’

                AND object_name LIKE ‘%Locks%’) h

     CROSS JOIN

     (SELECT (cntr_value – @PageSplitsPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [PageSplitsPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘Page Splits/sec’

                AND object_name LIKE ‘%Access Methods%’) i

     CROSS JOIN

     (SELECT * FROM @RatioStatsY

               WHERE counter_name = ‘Processes blocked’

               AND object_name LIKE ‘%General Statistics%’) j

     CROSS JOIN

     (SELECT (cntr_value – @CheckpointPagesPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [CheckpointPagesPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘Checkpoint Pages/sec’

                AND object_name LIKE ‘%Buffer Manager%’) k

To complete the automation process I need do two additional things. One is to create the table that will store these metrics, which can be accomplished by running the following CREATE TABLE statement. Note this code again assumes that there is a DBA database where the table will be created:

USE DBA;

GO

CREATE TABLE [dbo].[PerformanceMetricHistory](

      [BufferCacheHitRatio] [numeric](38, 13) NULL,

      [PageLifeExpectency] [bigint] NULL,

      [BatchRequestsPerSecond] [bigint] NULL,

      [CompilationsPerSecond] [bigint] NULL,

      [ReCompilationsPerSecond] [bigint] NULL,

      [UserConnections] [bigint] NULL,

      [LockWaitsPerSecond] [bigint] NULL,

      [PageSplitsPerSecond] [bigint] NULL,

      [ProcessesBlocked] [bigint] NULL,

      [CheckpointPagesPerSecond] [bigint] NULL,

      [StatDate] [datetime] NOT NULL

) ON [PRIMARY]

The second thing is to create a SQL Server Agent job that is schedule to run however often I want to capture these statistics. Below is the code to create a SQL Server Agent Job name ‘Collect Performance Metrics’ that runs every minute:

USE [msdb]

GO

/****** Object:  Job [Collect Performance Metrics]    Script Date: 07/22/2011 14:01:05 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 07/22/2011 14:01:05 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Collect Performance Metrics’,

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=0,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N’No description available.’,

            @category_name=N'[Uncategorized (Local)]’,

            @owner_login_name=N’sa’, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Collect Metrics]    Script Date: 07/22/2011 14:01:05 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Collect Metrics’,

            @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’INSERT INTO EXEC dbo.GetMetrics’,

            @database_name=N’DBA’,

            @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’Every Minute’,

            @enabled=1,

            @freq_type=4,

            @freq_interval=1,

            @freq_subday_type=4,

            @freq_subday_interval=1,

            @freq_relative_interval=0,

            @freq_recurrence_factor=0,

            @active_start_date=20110722,

            @active_end_date=99991231,

            @active_start_time=0,

            @active_end_time=235959

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:

Once all these components have been created, you can sit back and start tracking your performance metrics overtime. Keep in mind that this process will create many records in the PerformanceMetricHistory table. Therefore, if you use this process, you should think about how you might want to manage the size of this table over time. If left unchecked, you might find this table ends up taking up a lot of disk space. You might consider purging old statistics after they provide no value, or roll up the statistics to less detailed granularity after a month or two.

Collecting Performance Statistics

Collecting performance statistics over time and producing trend reports is something every DBA should be doing. By doing this a DBA can track the performance trends of their server over time. Knowing this allows a DBA to understand the resource utilization trend and therefore allows them to plan appropriately to make sure resources are always available, even if they need to purchase a new server to handle the increasing workload. 

What I have shown you here is the poor man’s method of capturing performance metrics. Keep in mind there are many third-party products out there, which will do the same thing as I described, and much more. If you are currently not collecting and monitoring performance metrics, then by deploying the solution available in this article will allow you a free and easy solution to collect and store performance metrics.

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles