I held a workshop on performance tuning in Portland, Oregon yesterday, and as part of that I mentioned a process I follow for automatically collecting Performance Monitor Counters for a system. I use this data to store a baseline and then subsequently to have a reference set of data to compare with.
I use the logman feature in the operating system, and I had some references there in my script, and I promised I would post the comments block at the top of the script.
Tomorrow I’ll post how I use the CSV file this collects and import it into SQL Server for analysis:
/* Perfmon Example.sql
Purpose: Imports Perfmon Data into SQL Server
Author: Buck Woody
Last Edited: 09/30/2009
Instructions: Proper Counters must be tracked. See table definition
for that structure.
You can also automate this: http://www.mssqltips.com/tip.asp?tip=1776
Another way: http://www.mssqltips.com/tip.asp?tip=1722
Counters to include: http://sqlblog.com/blogs/kevin_kline/archive/2008/03/28/the-most-important-performance-monitor-counters-for-sql-server.aspx
More Interpretations: http://technet.microsoft.com/en-us/magazine/2008.08.pulse.aspx
Logman automation - Comes with Windows OS:
logman create /?
logman start /?
logman stop /?
This one uses a file I have called “SQL Server Counters” – more on that later
logman start "SQL Server Counters"