By: Tim Ford | Comments (3) | Related: > Dynamic Management Views and Functions
Problem
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.
Solution
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:
USE [iDBA];
IF NOT EXISTS
(
SELECT name
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; 
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]
FROM
(
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]
) TEMP
WHERE rn = 1; 
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:
DELETE
FROM iDBA.dbo.[dm_os_performance_counters]
WHERE date_stamp < DATEADD(d, -3, GETDATE()) Next Steps
- The template tip mentioned in this article is available here
- Review the initial tip in the series
- More tips from the author are available via this link
- Review additional SQL Server tips from this author at his personal blog: thesqlagentman.com
About the author
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
