Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Collecting SQL Server performance counter data for trending


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

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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;

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]
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;

 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:

DELETE
FROM
iDBA.dbo.[dm_os_performance_counters]
WHERE date_stamp < DATEADD(d, -3, GETDATE())
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Hi,

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

Excellent job Tim.

 

Thanks

Thomas


Thursday, August 12, 2010 - 9:25:43 AM - Kevin Back To Top
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






Learn more about SQL Server tools