Windows – Performance Monitor w/ SQL Server – how many counters is too many

performanceperformance-monitoringsql serversql-server-2008windows

I'm doing some performance analysis using the built-in Windows Performance Monitor, both on general hardware and SQL Server. I've been reading a lot about which performance counters to use; in particular this document on the waits and queues method is great.

However, it's recommending an awful lot of counters and I'm concerned that if I have too many then either my production server will fall over, or the results will be too skewed to get an accurate reading.

I don't know enough about what's actually going on to generate or gather these statistics – what kind of load do they add to a system generally? I know, the answer is "it depends", on the hardware and the current load, but in general terms I'm wondering if there's a consensus on how many is definitely too many – 20, 50, 100 or more at once?

EDIT: In case it's of relevance, I have 41 counters currently configured:

\Memory\Page Faults/sec
\Memory\Pages/sec
\PhysicalDisk(_Total)\% Disk Time
\PhysicalDisk(_Total)\Avg. Disk Queue Length
\PhysicalDisk(_Total)\Disk Reads/sec
\PhysicalDisk(_Total)\Disk Writes/sec
\Process(sqlservr)\% Privileged Time
\Process(sqlservr)\% Processor Time
\Process(sqlservr)\% User Time
\Process(sqlservr)\Page Faults/sec
\Processor(_Total)\% Processor Time
\Processor(_Total)\Interrupts/sec
\System\Processor Queue Length
\SQLServer:Access Methods\Full Scans/sec
\SQLServer:Access Methods\Index Searches/sec
\SQLServer:Access Methods\Page Splits/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Checkpoint pages/sec
\SQLServer:Buffer Manager\Lazy writes/sec
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:Buffer Manager\Page reads/sec
\SQLServer:Buffer Manager\Page writes/sec
\SQLServer:Databases(_Total)\Log Flush Wait Time
\SQLServer:Databases(_Total)\Log Flush Waits/sec
\SQLServer:Databases(_Total)\Transactions/sec
\SQLServer:General Statistics\User Connections
\SQLServer:Latches\Average Latch Wait Time (ms)
\SQLServer:Latches\Latch Waits/sec
\SQLServer:Locks\Average Wait Time (ms)
\SQLServer:Locks(_Total)\Lock Wait Time (ms)
\SQLServer:Locks(_Total)\Lock Waits/sec
\SQLServer:Memory Manager\Memory Grants Pending
\SQLServer:Memory Manager\Memory Grants Outstanding
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:Memory Manager\Total Server Memory (KB)
\SQLServer:Plan Cache\Cache Hit Ratio
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\Auto-Param Attempts/sec
\SQLServer:SQL Statistics\Failed Auto-Params/sec

Best Answer

I can't give you a magic number, however I can tell you that the overhead on performance counters is very very low. The information is already there, and Microsoft fully intends for you to be using them and collecting them. The machine doesn't have to go out of it's way to generate them all it does is capture them instead of letting them slide on by if you choose to add them. I can tell you that we've got 75 on our production machine and see no difference in load.

Related Topic