|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\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:
Hope everybody now can use that poster much easier.