« Understanding your baselines - Perfmon Batch Requests/sec and Transactions/sec | Main | Baseline your SQL Servers - Wait Stats »
Friday
Jan242014

Baseline your SQL Servers - PerfMon counters

I wanted a daily e-mailed report of the performance counters exposed in the sys.dm_os_performance_counters DMV sliced by day-of-week and time-of-day. So I came up with the following solution.


Note that I'm reporting on 21 different counters which are represent either cumulative or "actual observed" values. There's other counters and other types of counters you can capture as well. Depending on the type of counter you may just be able to grab the current value (e.g. Page life expectancy) and that will tell you what the Page life expectancy reading is at the time you took the sample. For other types (i.e Page reads/sec) you may need to take the difference of two consecutive readings and divide the result by the number of seconds between the two readings.


All cntr_type's and methods for evaluating them are described in this great article (with whom I'm not affiliated with in any way).


If you include other cntr_type's you'll need to modify the [PerfMonArchivePopulate] SPROC and possibly add additional columns to the [PerfmonDelta] table outlined below. Capturing values of different counters will require changes to the same SPROC's.


If you decide to capture counter data more/less frequently than 300 seconds it will simply require a change to the @SecondsBetweenCaptures in the [PerfMonArchivePopulate] SPROC.


Finally I'm basing the report on the last seven days' worth of data and retaining the data for 60 days. Increasing the scope of the data evaluated for the report will probably add duration and resource utilization to your daily report.


I'm assuming you have a database created to store performance data like this. In the examples below I'm using one called [Baseline].


Here are the 21 counters I want to report on:

Create a table for these values [PerfmonDelta]:

Populate [PerfmonDelta] with the counters you wish to track:

Create a table to store the captured data in:

Consider the following indexes for the [CaptureArchive] table. These are in addition to the clustered index based on the PK:

Create a SPROC to capture the data. Note that you'll need to modify this if you decide to report on different counters and/or a different frequency for how often data is captured:

Create a SPROC to clean up old captures:

Create a SPROC to send your daily e-mail. As written it'll handle 21 counters and the scope of the report will be the last seven days. I've noted in the comments where to modify either of these:

Finally schedule the SPROC's to run.

Schedule your [PerfMonArchivePopulate] SPROC to run every, say, five minutes
Schedule your e-mail report to run daily followed immediately by your cleanup SPROC (the [PerfmonStatsAlertHistoric] and [PerfMonStatsCleanup] SPROC's respectively

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>