If you cannot afford a third-party tool that can capture database performance metrics or don’t want to roll your own solution, then using T-SQL to collect some performance metrics for SQL Server may be your solution. .
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.
