Collecting SQL Server performance counter data for trending

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


In my previous tip in this series, I introduced you to the sys.dm_os_performance_counters DMV and showed you how to return information from it. This data is displayed with the current values, so what happens if you want to look at counters over time? In this tip I show how you can use the DMV to collect data for trending.


In order to accomplish this you will need to persist results from the sys.dm_os_performance_counters DMV to a permanent table over time. I tend to do this on an as-needed basis with only the counters I'm interested in monitoring. For the purpose of this example I will continue to use the counters associated with the SQLServer:Buffer Manager object that I was utilizing in my first tip in this series. I'll store this information inside the monitoring DB I have created on each of my SQL Server instances. I call mine iDBA as those who have read my tips before may remember.

To persist the counters I'm interested in (SQLServer:Buffer Manager in this case) I would run the following query inside of a SQL Server Agent job set to run every 30 minutes. Your frequency times may vary depending upon your individual situations:


FROM sys.[tables]
WHERE [name] = 'dm_os_performance_counters'

CREATE TABLE dbo.dm_os_performance_counters
[object_name] VARCHAR(128), [counter_name] VARCHAR(128),
[instance_name] VARCHAR(128), [cntr_value] bigint,
[date_stamp] datetime

INSERT INTO iDBA.dbo.dm_os_performance_counters
([object_name], [counter_name], [instance_name],
[cntr_value], [date_stamp])
SELECT [object_name], [counter_name], [instance_name],
[cntr_value], GETDATE()
FROM sys.[dm_os_performance_counters]
WHERE [object_name] = 'SQLServer:Buffer Manager';

Now that we've been collecting data for some time (not necessarily in this case on my test laptop so we will pretend we are) let's look at various methods for reviewing information that we've persisted from sys.dm_os_performance_counters.

Return history for a specific counter

SELECT [object_name], [counter_name],
[instance_name], [cntr_value], [date_stamp]
FROM iDBA.[dbo].[dm_os_performance_counters]
WHERE [counter_name] = 'Page lookups/sec'
ORDER BY date_stamp DESC;

let's look at various methods for reviewing information that we've persisted from sys.dm_os_performance_counters.

This is a simple query, but what if you want to look at the most recent values for the entire last polling time for the complete list of counters for a specific object?

Return list of counter values for last polling period persisted to disk

This does get a little more confusing because we need to bring the ROW_NUMBER, OVER, and PARTITION BY syntax into play in order to accomplish the task. Take for example the following query, which returns the list of all SQLServer:Buffer Manager counters for the last time they were persisted to the table we've created to hold historical counter values.

SELECT [object_name], [counter_name], [instance_name], [cntr_value]
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [date_stamp],
ROW_NUMBER() OVER(PARTITION BY [counter_name] ORDER BY [date_stamp] DESC) AS 'rn'
FROM [iDBA].dbo.[dm_os_performance_counters]
rn = 1;

 use the counters associated with the SQLServer:Buffer Manager

The ROW_NUMBER() function is applied against a partition by the counter_name column and then ordered by the date_stamp column. This means that a sequential row number is assigned for each combination of those columns, ordered by date_stamp. The row number is reset upon every change in the counter_name column. Since we are only interested in the most recent value, we are going to only return results where the row number = 1.

PARTITION BY is a windowed function. It would appear to make more sense if I was to structure the query as follows, but you will receive the associated error:

SELECT [object_name], [counter_name], [instance_name], [cntr_value]
FROM [iDBA].dbo.[dm_os_performance_counters]
WHERE ROW_NUMBER() OVER(PARTITION BY [counter_name] ORDER BY [date_stamp] DESC) = 1;

Msg 4108, LEVEL 15, State 1, Line 3
Windowed functions can ONLY appear IN the SELECT OR ORDER BY clauses.

A simple solution to this issue is to wrap the original query inside a select statement and that is why I structured the query as I did originally.

Data cleanup

Finally, there is a very important step if you decide to persist counter data: cleanup. I prefer to only store a few days worth of data to avoid filling my database and eventually the SQL Server instance with stale (and irrelevant, after a while) data. Therefore I employ the following query, included as an additional step in any SQL Agent job I use to collect data from sys.dm_os_performance_counters:

WHERE date_stamp < DATEADD(d, -3, GETDATE())
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

Friday, November 29, 2013 - 3:55:20 AM - Cherry Back To Top (27636)


Please provide tip on how to setting this up on a remote server rather than the same server.

Friday, June 24, 2011 - 10:37:37 AM - Thomas LeBlanc Back To Top (14076)

Excellent job Tim.




Thursday, August 12, 2010 - 9:25:43 AM - Kevin Back To Top (10045)
These are great tips, Tim.  How do you feel about the free counter collection tools out there like PAL and SQLH2?  Ever used any of those?  Best regards,  -Kev

get free sql tips
agree to terms