Monday, June 23, 2014

How to Automate collecting Perfmon Counters of Interest

For more than two years I've tried to use wonderful poster made by Kevin Kline and Brent Ozar.
Click to get the poster

I've got several of these posters on SQL Saturday events. I've redistributed them between my friends and coworkers, but never was really able to use it in full capacity.

There are almost 100 counters that you have to setup in Perfmon and after setting just 10 you are alreeady lost.

 Last week I attended presentation of Kun Lee about automation of perfmon on Baltimore SQL Server User Group meeting. Kun showed how to use configuration file to set perfmon counters and I've used that technic for "Counters of Interest".

 Based on the Poster I've created "SQLDataCollector.config" file.
Inside it looks like this:
 "\\YOUR_SQL_SERVER\Memory\Available MBytes"
"\\YOUR_SQL_SERVER\Memory\Pages Input/sec" "\\YOUR_SQL_SERVER\Memory\Pages/sec" "\\YOUR_SQL_SERVER\Paging File\% Usage" "\\YOUR_SQL_SERVER\Paging File\% Usage Peak" "\\YOUR_SQL_SERVER\Process(sqlservr)\% Processor Time" "\\YOUR_SQL_SERVER\Process(msmdsrv)\% Processor Time" "\\YOUR_SQL_SERVER\Processor(_Total)\% Processor Time" "\\YOUR_SQL_SERVER\Processor(_Total)\% Privileged Time"

You have to replace word "YOUR_SQL_SERVER" by name of your SQL Server or by it's IP address.

Then I used simple "loading" command:
logman create counter BASELINE -f csv -max 200 -si 00:01:00 -b 06/19/2014 11:06:00AM  -v mmddhhmm -o "C:\Temp\Test\Perfmon\Perfmon.csv" -cf "C:\Temp\Test\Perfmon\SQLDataCollector.config"

In order to use it you have to modify following:
-0 - Location and name of resulting trace file;
-cf - Location of config file;  
-b Date and time when you want to run your monitoring. If you want to start trace manually you can start perfmon.exe and run your trace from there:

Also, you can adjust data collecting interval which in mi case set as reccomended by Kun Lee a minute  (-si). There are much more options of "logman" to choose from, you can even set tracing to SQL Server. 

Hope everybody now can use that poster much easier.

No comments:

Post a Comment