Capturing baselines

Capturing baselines seems to be a hot topic in SQL Server land at the moment. So I thought I’d write a post on how I do it.

When I first looked at doing this I read this post from Brent Ozar http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/ . So taking the list of counters he suggests I decided I’d look to see if there was a way that I could automate a system whereby I capture the stats and then import them into a database for analysis. Initially I was going to write an SSIS package to import the CSV’s into a database, however when I was playing around with perfmon I noticed that you can infact import the data straight into SQL Server. And this is how you do it:

Start perfmon by going to Start > Run > perfmon

Create a new collection set set

Give the set a name

Select the type of data we want to add (performance counters)

Click add

Add the counters using the dialog

When finished adding counters click OK. Then click next to carry on with the wizard.

Specify a dummy directory for storing the output (we’re going to change this in a bit).

Specify the user that the collector runs as (I believe they need to be a local admin).

Finally click finish and you set should be created.

Next we need to setup an odbc connection so that perfmon can insert the information into our database. I’ve setup a DB called perfmon DB on a dev instance for the purposes of this post

Go to Control Panel > Admin Tools > Data Sources (ODBC) and then click on the system DSN tab.

Click Add

I select SQL Server, I’ve had permissions problems setting up an ODBC connection with the Native Clients that I was unable to resolve at the time of writing…

Give the DSN a name and description, and specify the server you want to write the data to.

Specify the credentials to login to the server

Specify the database we’re going to store the info in

Final options for the DSN

And that should be the DSN created once you click finish

Return to the perfmon screen and highlight your SQLBaselineSet

Go to the properties of the Data Collector 01

Change the logging type from Binary to SQL

Specify the DSN we created in the bottom drop down box and click ok

The only things that remains really is to start the logging!

After you’ve started the logging, assuming all your permissions are set correctly for the DSN user etc. you should get 3 tables in your database

What I did after this was to look at the tables and create a view that would allow for better interpretation of the data. Code below by way of an example. If you used different counters then you’ll have to adjust the code accordingly.

CREATE VIEW [dbo].[PerfCounters]

 AS

 SELECT

 cd.CounterDateTime,

 MAX(CASE WHEN cde.CounterID = 1 THEN CounterValue END) AS [Available MBytes],

 MAX(CASE WHEN cde.CounterID = 10 THEN CounterValue END) AS [% Usage\??C:pagefile.sys],

 MAX(CASE WHEN cde.CounterID = 11 THEN CounterValue END) AS [% Usage_Total],

 MAX(CASE WHEN cde.CounterID = 12 THEN CounterValue END) AS [Avg. Disk sec/Read\0 C: E: D:],

 MAX(CASE WHEN cde.CounterID = 13 THEN CounterValue END) AS [Avg. Disk sec/Read1 G:],

 MAX(CASE WHEN cde.CounterID = 14 THEN CounterValue END) AS [Avg. Disk sec/Read_Total],

 MAX(CASE WHEN cde.CounterID = 15 THEN CounterValue END) AS [Avg. Disk sec/Write\0 C: E: D:],

 MAX(CASE WHEN cde.CounterID = 16 THEN CounterValue END) AS [Avg. Disk sec/Write1 G:],

 MAX(CASE WHEN cde.CounterID = 17 THEN CounterValue END) AS [Avg. Disk sec/Write_Total],

 MAX(CASE WHEN cde.CounterID = 18 THEN CounterValue END) AS [Disk Reads/sec\0 C: E: D:],

 MAX(CASE WHEN cde.CounterID = 19 THEN CounterValue END) AS [Disk Reads/sec1 G:],

 MAX(CASE WHEN cde.CounterID = 2 THEN CounterValue END) AS [Buffer cache hit ratio],

 MAX(CASE WHEN cde.CounterID = 20 THEN CounterValue END) AS [Disk Reads/sec_Total],

 MAX(CASE WHEN cde.CounterID = 21 THEN CounterValue END) AS [Disk Writes/sec\0 C: E: D:],

 MAX(CASE WHEN cde.CounterID = 22 THEN CounterValue END) AS [Disk Writes/sec1 G:],

 MAX(CASE WHEN cde.CounterID = 23 THEN CounterValue END) AS [Disk Writes/sec_Total],

 MAX(CASE WHEN cde.CounterID = 3 THEN CounterValue END) AS [Page life expectancy],

 MAX(CASE WHEN cde.CounterID = 4 THEN CounterValue END) AS [User Connections],

 MAX(CASE WHEN cde.CounterID = 40 THEN CounterValue END) AS [% Processor Time_Total],

 MAX(CASE WHEN cde.CounterID = 5 THEN CounterValue END) AS [Memory Grants Pending],

 MAX(CASE WHEN cde.CounterID = 6 THEN CounterValue END) AS [Batch Requests/sec],

 MAX(CASE WHEN cde.CounterID = 7 THEN CounterValue END) AS [SQL Compilations/sec],

 MAX(CASE WHEN cde.CounterID = 8 THEN CounterValue END) AS [SQL Re-Compilations/sec],

 MAX(CASE WHEN cde.CounterID = 9 THEN CounterValue END) AS [Processor Queue Length]

 FROM

 counterdata cd

 JOIN dbo.CounterDetails cde ON cd.CounterID = cde.CounterID

 GROUP BY

 cd.CounterDateTime

Which should allow for queries like this

What you do from here is up to you but I hope you’ve found this useful.