Avoid all use of the Performance Monitor interface by using typeperf.exe (the oddest of names).
- Brent Ozar’s Tutorial on SQL Performance Monitoring – using performance monitor manually to gather SQL-relevant counters.
- Shaun J Stuart Baselining SQL Server Part 1 and Part 2 – more or less all you need to know about using typeperf for the same job.
The Baselining articles above are thorough. These are notes on my implementation.
The aim of the articles is to log to a database and then run reports from the database. A change to the -o switch will log the output to a file instead.
TYPEPERF -c "\System\Processor Queue Length" -si 5 -sc 4
Emits the “System\Processor Queue Length” counter, four times with 5 second intervals (including 5 seconds before the first counter is emitted).
Which counters are available?
TYPEPERF -q > c:\BitBucket\TypePerfAvailableCounters.txt
Outputs a list of all counters available on the machine to a handy text file.
The only intelligent approach is to collect the counters you are interested in into a text file. This is the list, aimed at SQL performance monitoring, that I am currently using.
Note on the wildcards. …PhysicalDisk(*).. will return counters for all disks (0 C:, 1 E, …) plus an aggregated counter named _Total. I don’t want the _Total counter; the expression …PhysicalDisk(* *) matches all counters with a space in the identifier – which the disk names have but that the _Total name doesn’t. Unfortunately the same hack doesn’t work for SQL Server:Locks(*) since most of the lock identifiers don’t have spaces.
TYPEPERF -cf "C:\PerformanceMonitoring\CounterList.txt" -si 15 -sc 4 is a command that will use a CounterList file.
TYPEPERF -cf "C:\PerformanceMonitoring\CounterList.txt" -si 15 -sc 4 -f SQL -o SQL:DatabaseStatisticsDS!Log1 will log to database using System DSN “DatabaseStatisticsDS” (creating the database if necessary).
You can gather counters from another server by using the -s option. I haven’t attempted to do that yet; so far I am running typeperf against the local machine and logging the output to a distant one.
User must be member of Performance Log Users group. No errors raised if they aren’t but some (not all) counters will be zero.
The Baselining SQL Server article Part 1 covers TypePerformance errors (“No valid counters” !) in detail.