Collecting Performance Metrics Using SQL Server DMV

What method do you use to determine how well your server is performing overtime? Do you know what your batch request per Second looks like over the last 30 days, or year? Can you track your page life expectancy over the day? As a DBA, there are always many things to do, and sometimes tracking your database performance is one of the last things you have time to work on. There are many different ways to collect performance related numbers for SQL Server. If you work in an organization that can afford it, I would suggest you buy a third-party tool that can capture performance metrics. If you cannot afford an expensive tool, or like rolling your own solution, then I will explain in this article how to use T-SQL to collect some performance metrics for SQL Server.

The Method

There are many different methods you might consider when collecting performance metric. However, I am only going to focus in and explain one method. That method is to use sys.dm_os_performanace_counters, which is a dynamic management view (DMV).

The sys.dm_os_performanace_counters DMVs was introduction in SQL Server 2005, and is still available in SQL Server 2008. This DMV displays one record for every counter that the server collects. You need "VIEW SERVER STATE" permissions to use this DMV. Unless your login is in the sysadmin server role, or someone has specifically provided you "VIEW SERVER STATE" permissions you might not be able to use this DMV. If you do not have permissions to query this DMV then I would just ask your administrator, to GRANT you "VIEW SERVER STATE" permission. I hope that they will not have an issue allowing view permissions to the server state.

Using sys.dm_os_peformance_counters DMV

It is not exactly straightforward to understand how to get each performance counter out of this DMV. Some performance counters can be extracted from a single record, while other counters require you to use two different records to gain the performance counter value. Others require you to run this DMV twice and then compare the counters between both executions to obtain a useful performance metric.

To see all the counters available run the following command:

SELECT * FROM sys.dm_os_performance_counters;

When you run this command, you should get a list of many counters, as displayed below:
(Note, below I have selectively only provided a few somewhat random rows of the output from the command above).

object_name                    counter_name                instance_name   cntr_value  cntr_type
------------------------------ --------------------------- --------------- ----------- ----------
MSSQL$SQL2008:Buffer Manager   Buffer cache hit ratio                      1055        537003264
MSSQL$SQL2008:Buffer Manager   Buffer cache hit ratio base                 1055        1073939712
MSSQL$SQL2008:Buffer Manager   Page lookups/sec                            6057902     272696576
MSSQL$SQL2008:Latches          Average Latch Wait Time(ms)                 74743       1073874176
MSSQL$SQL2008:Databases        Data File(s) Size (KB)      AventureWorksLT 5312        65792

If you examine the output above, you will see each row has a different "cntr_type" value. You need to understand each of these cntr_value's to understand how to interpret this output. Below I will explain how to use the cntr_value column for each of these different cntr_types to obtain a meaningful performance metric.

If you look at the first two lines, you can see that there is first a cntr_type of "537003264", which has a name of "Buffer cache hit ratio" and then in the second line there is a cntr_type of "1073939712" with a name of "Buffer cache hit ratio base". These two types of counters have to be used in conjunction with each other to determine the actual buffer cache hit ratio performance measurement. In the example output above, I show the buffer cache hit ratio counter pairing. There are many pairings of a counter with a base when you look at all the performance counters exposed by the sys.dm_os_performance_counters DMV. To calculate the real counter value, or in my case the buffer cache hit ratio you need to take the cntr_value "1055" of the counter_name "Buffer cache hit ratio" and divide it by the "Buffer cache hit ratio base" counter_value, which in this case is also "1055" and then multiple by 100. Here is an example that does that in a single statement:

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 = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +
':Buffer Manager' END ) a
CROSS JOIN
(SELECT * from sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
and object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +
':Buffer Manager' END ) b;

All the per-second performance counters have a cntr_type = 272696576. In my example above, the per-second counter I displayed was "Page Lookups / sec". For these types of counters the cntr_value contains a cumulative number. Therefore, in order to calculate the actual per-second rate you need to capture the per-second cntr_value twice and then calculate the per-second amount based on the two cntr_value's and the number of seconds between the two samplings. Here is an example of how to calculate the per-second counter value for the number of "Page reads/sec":

-- Collect first sample
DECLARE @old_cntr_value INT;
DECLARE @first_sample_date DATETIME;
SELECT @old_cntr_value = cntr_value,
@first_sample_date = getdate()
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page reads/sec';
-- Time frame to wait before collecting second sample
WAITFOR DELAY '00:00:10'
-- Collect second sample and calculate per-second counter
SELECT (cntr_value - @old_cntr_value) /
DATEDIFF(ss,@first_sample_date, GETDATE()) as PageReadPerSec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page reads/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END

You can see that I started this code by first capturing a sample of the cntr_value for the "Page reads/sec" counter, and the time of the sampling. I saved the counter value and sampling time values in local variables. Next, I used the "WAITFOR" statement to wait 10 seconds before capturing the second sample. While I captured the second sample, I calculated the actual per-second value. I did this by first subtracting my saved cntr_value (@old_cntr_value) from the cntr_value provided by the second sampling. By doing this, I calculated the total number of page reads performed between the two samples. I then took the total number of page reads performed and divide by the number of seconds between the two samplings. The time difference between the two samplings was calculated using the date DIFF function. If you want to capture per-second counter values using the sys.dm_os_performance_counters you will need to use a method similar to what I describe above for each per-second counter you want to capture, to determine the actual per-second value.

The cntr_type " 1073874176" and the "65792" values, like the "Average Latch Wait Time (ms)" and "Data Files(s) Size (KB)" counters stand by themselves, meaning no calculation is needed to calculate the actual value. You can grab the cntr_value directly for these cntr_type's and they will represent the value associated with the counter_name. If you look at the counter_name "Data File(s) Size (KB)" in my output above you will see that the size of my "AdventureWorksLT" database is 5,312 KB.

As you probably already noticed the cntr_type "65792" in my example has a value under the "instance_name" column, in this example that value is "AdventureWorksLT". This column not only shows the names of databases, but many different other values like, "Application", "Page", "_ Total", depending on the particular counter being displayed.

Script to collect Some Performance Metrics

One of the things that you can use the sys.dm_os_performanace_counters for is to gather perform metrics over time. Below is a script that can be used to display a number different performance metrics. Capturing these performance metrics periodically allows you to produce trend reports over time for each counter:

DECLARE @SQLProcessUtilization int;
DECLARE @PageReadsPerSecond bigint
DECLARE @PageWritesPerSecond bigint
DECLARE @CheckpointPagesPerSecond bigint
DECLARE @LazyWritesPerSecond bigint
DECLARE @BatchRequestsPerSecond bigint
DECLARE @CompilationsPerSecond bigint
DECLARE @ReCompilationsPerSecond bigint
DECLARE @PageLookupsPerSecond bigint
DECLARE @TransactionsPerSecond bigint
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
)
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
SET @stat_date = getdate()
SELECT TOP 1 @PageReadsPerSecond=cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Page reads/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END
SELECT TOP 1 @PageWritesPerSecond= cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Page writes/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END
SELECT TOP 1 @CheckpointPagesPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Checkpoint pages/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END
SELECT TOP 1 @LazyWritesPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Lazy writes/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END
SELECT TOP 1 @BatchRequestsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Batch Requests/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:SQL Statistics'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics' END
SELECT TOP 1 @CompilationsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'SQL Compilations/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:SQL Statistics'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics' END
SELECT TOP 1 @ReCompilationsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'SQL Re-Compilations/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:SQL Statistics'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics' END
SELECT TOP 1 @PageLookupsPerSecond=cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Page lookups/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:Buffer Manager'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END
SELECT TOP 1 @TransactionsPerSecond=cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Transactions/sec' AND instance_name = '_Total'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:Databases'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Databases' END
-- Wait for 5 seconds before taking second sample
WAITFOR DELAY '00:00:05'
-- 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.[PageReadPerSec] [PageReadsPerSec]
,d.[PageWritesPerSecond] [PageWritesPerSecond]
,e.cntr_value [UserConnections]
,f.cntr_value [PageLifeExpectency]
,g.[CheckpointPagesPerSecond] [CheckpointPagesPerSecond]
,h.[LazyWritesPerSecond] [LazyWritesPerSecond]
,i.cntr_value [FreeSpaceInTempdbKB]
,j.[BatchRequestsPerSecond] [BatchRequestsPerSecond]
,k.[SQLCompilationsPerSecond] [SQLCompilationsPerSecond]
,l.[SQLReCompilationsPerSecond] [SQLReCompilationsPerSecond]
,m.cntr_value [Target Server Memory (KB)]
,n.cntr_value [Total Server Memory (KB)]
,GETDATE() AS [MeasurementTime]
,o.[AvgTaskCount]
,o.[AvgRunnableTaskCount]
,o.[AvgPendingDiskIOCount]
,p.PercentSignalWait AS [PercentSignalWait]
,q.PageLookupsPerSecond As [PageLookupsPerSecond]
,r.TransactionsPerSecond AS [TransactionsPerSecond]
,s.cntr_value [MemoryGrantsPending]
FROM (SELECT *, 1 x FROM @RatioStatsY
WHERE counter_name = 'Buffer cache hit ratio'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END ) a
join
(SELECT *, 1 x FROM @RatioStatsY
WHERE counter_name = 'Buffer cache hit ratio base'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END ) b
on a.x = b.x
join
(SELECT (cntr_value - @PageReadsPerSecond) / (CASE WHEN datediff(ss,@stat_date, getdate()) = 0 THEN 1 
	ELSE datediff(ss,@stat_date, getdate()) end) as [PageReadPerSec], 1 x
FROM @RatioStatsY
WHERE counter_name = 'Page reads/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END
)c on a.x = c.x
join
(SELECT (cntr_value - @PageWritesPerSecond) / (CASE WHEN datediff(ss,@stat_date, getdate()) = 0 THEN 1 
	ELSE datediff(ss,@stat_date, getdate()) end) as [PageWritesPerSecond], 1 x
FROM @RatioStatsY
WHERE counter_name = 'Page writes/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END
) d on a.x = d.x
join
(SELECT *, 1 x FROM @RatioStatsY
WHERE counter_name = 'User Connections'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:General Statistics' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':General Statistics' END ) e
on a.x = e.x
join
(SELECT *, 1 x FROM @RatioStatsY
WHERE counter_name = 'Page life expectancy '
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager' ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END ) f
on a.x = f.x
join
(SELECT (cntr_value - @CheckpointPagesPerSecond) / (CASE WHEN datediff(ss,@stat_date, getdate()) = 0 THEN 1 
	ELSE datediff(ss,@stat_date, getdate()) end) as [CheckpointPagesPerSecond], 1 x
FROM @RatioStatsY
WHERE counter_name = 'Checkpoint pages/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END
) g on a.x = g.x
join
(SELECT (cntr_value - @LazyWritesPerSecond) / (CASE WHEN datediff(ss,@stat_date, getdate()) = 0 THEN 1 
	ELSE datediff(ss,@stat_date, getdate()) end) as [LazyWritesPerSecond], 1 x
FROM @RatioStatsY
WHERE counter_name = 'Lazy writes/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END
) h
on a.x = h.x
join
(SELECT *, 1 x FROM @RatioStatsY
WHERE counter_name = 'Free Space in tempdb (KB)'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Transactions' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Transactions' end) i
on a.x = i.x
join
(SELECT (cntr_value - @BatchRequestsPerSecond) / (CASE WHEN datediff(ss,@stat_date, getdate()) = 0 THEN 1 
	ELSE datediff(ss,@stat_date, getdate()) end) as [BatchRequestsPerSecond], 1 x
FROM @RatioStatsY
WHERE counter_name = 'Batch Requests/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:SQL Statistics' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics' END
) j
on a.x = j.x
join
(SELECT (cntr_value - @CompilationsPerSecond) / (CASE WHEN datediff(ss,@stat_date,getdate()) = 0 THEN 1 
	ELSE datediff(ss,@stat_date, getdate()) end) as [SQLCompilationsPerSecond], 1 x
FROM @RatioStatsY
WHERE counter_name = 'SQL Compilations/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:SQL Statistics' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics' END
) k on a.x = k.x
join
(SELECT (cntr_value - @ReCompilationsPerSecond) / (CASE WHEN datediff(ss,@stat_date, getdate()) = 0 THEN 1 
	ELSE datediff(ss,@stat_date, getdate()) end) as [SQLReCompilationsPerSecond], 1 x
FROM @RatioStatsY
WHERE counter_name = 'SQL Re-Compilations/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:SQL Statistics' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics' END
) l
on a.x = l.x
join
(SELECT *, 1 x FROM @RatioStatsY
WHERE counter_name = 'Target Server Memory (KB)'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Memory Manager' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Memory Manager' END ) m
on a.x = m.x
join
(SELECT *, 1 x FROM @RatioStatsY
WHERE counter_name = 'Total Server Memory (KB)'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Memory Manager' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Memory Manager' END ) n
on a.x = n.x
JOIN
(SELECT 1 AS x
, AVG(current_tasks_count)AS [AvgTaskCount]
, AVG(runnable_tasks_count)AS [AvgRunnableTaskCount]
, AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255) o
on a.x = o.x
JOIN
( SELECT 1 AS x, SUM(signal_wait_time_ms) / sum (wait_time_ms) AS PercentSignalWait
FROM sys.dm_os_wait_stats) p
ON a.x = p.x
join
(SELECT (cntr_value - @PageLookupsPerSecond) / (CASE WHEN datediff(ss,@stat_date, getdate()) = 0 THEN 1 
	ELSE datediff(ss,@stat_date, getdate()) end) as [PageLookupsPerSecond], 1 x
FROM @RatioStatsY
WHERE counter_name = 'Page Lookups/sec'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Buffer Manager' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager' END
) q
on a.x = q.x
join
(SELECT (cntr_value - @TransactionsPerSecond) / (CASE WHEN datediff(ss,@stat_date, getdate()) = 0 THEN 1 
	ELSE datediff(ss,@stat_date, getdate()) end) as [TransactionsPerSecond], 1 x
FROM @RatioStatsY
WHERE counter_name = 'Transactions/sec' AND instance_name = '_Total'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:Databases'
ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Databases' END ) r
on a.x = r.x
join
(SELECT *, 1 x FROM @RatioStatsY
WHERE counter_name = 'Memory Grants Pending'
AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer:Memory Manager' 
	ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Memory Manager' END ) s
on a.x = s.x

By looking at this code, or running it on your system you will find that it displays one record. In the output of that one record, you will find a number of different performance counter metrics. To obtain these metrics you can see that I created a couple of temporary tables, @RatioStatsX and @RatioStatsY. These tables were used to collect two different samples of statistics from sys.dm_os_performance_counters DMV. Between collecting the two samples, I waited 5 seconds. This wait was performed so I could calculate those per-second counter values.

I have incorporated this code into a stored procedure that inserts this data into a physical table. This stored procedure is then run periodically (via SQL Agent) so I can capture performance metrics over time. By doing this, I was able to save the output of each run of my SQL Agent job in a SQL Server table. Now that I have my performance metrics stored in a table, I am able to create trend reports for any one of these collected performance statistics over time

Monitoring Performance with sys.dm_os_performance_counters DMV

Using the sys.dm_os_performance_counters DMV provides you a wealth of performance data that is only a T-SQL query away. By looking at different counters, you get a glimpse at the current performance status of your SQL Server instance. Collecting these performance counters overtime allows you to measure your performance trends overtime. Being able to understand your SQL Server performance trend allows you to you better manage your SQL Server instance performance.

» See All Articles by Columnist Gregory A. Larsen