Performance Monitoring using typeperf.exe
Introduction
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.
Implementation
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.
Command Line
Simplest example:
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.
\Memory\Available MBytes\Memory\Pages/sec\PhysicalDisk(* *)\% Disk Time\PhysicalDisk(* *)\Current Disk Queue Length\PhysicalDisk(* *)\Disk Transfers/sec\PhysicalDisk(* *)\Disk Bytes/sec\PhysicalDisk(* *)\Disk Reads/sec\PhysicalDisk(* *)\Disk Writes/sec\PhysicalDisk(* *)\Avg. Disk sec/Read\PhysicalDisk(** *)\Avg. Disk sec/Write\Processor(* *)\% Processor Time\Processor(* *)\% Privileged Time\System\Processor Queue Length\System\Context Switches/sec\Network Interface(* *)\Bytes Total/sec\SQLServer:Access Methods\FreeSpace Scans/sec\SQLServer:Access Methods\Full Scans/sec\SQLServer:Buffer Manager\Buffer cache hit ratio\SQLServer:Buffer Manager\Free pages\SQLServer:Buffer Manager\Page life expectancy\SQLServer:Latches\Total Latch Wait Time (ms)\SQLServer:Locks(*)\Lock Timeouts/sec\SQLServer:Locks(*)\Lock Wait Time (ms)\SQLServer:Locks(*)\Number of Deadlocks/sec\SQLServer:Memory Manager\Memory Grants Pending\SQLServer:Memory Manager\Target Server Memory (KB)\SQLServer:Memory Manager\Total Server Memory (KB)\SQLServer:SQL Statistics\Batch Requests/sec\SQLServer:SQL Statistics\SQL Re-Compilations/sec\SQLServer:SQL Statistics\SQL Compilations/sec\SQLServer:General Statistics\User Connections |
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.
Permissions
User must be member of Performance Log Users group. No errors raised if they aren’t but some (not all) counters will be zero.
Errors
The Baselining SQL Server article Part 1 covers TypePerformance errors (“No valid counters” !) in detail.