0

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
Improve this question
1
  • Do you install the performance data warehouse? Always a good thing to do. Commented Oct 7, 2010 at 10:19

4 Answers 4

Reset to default
2

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.

Improve this answer
2
  • Thanks, I'm similarly seeing zero difference after putting those 41 counters on. This is akin to magic for me. :) Commented Oct 7, 2010 at 10:04
  • 1
    Yeah, as long as your polling interval is reasonable you can add a ton of them. Commented Oct 8, 2010 at 4:55
3

Unless your capturing 1000 counters every second, I don't imagine you'll see a performance hit on the server. My recommendation: focus on how you are going to use them.

I analyze my trace results in Excel, so I always save to CSV format, and make sure I'm capturing fewer than 255 counters (due to the column limit in Excel).

It will likely take some time for you to identify which counters are useful to you, but once you do, it doesn't do you any good to capture extra columns. I used to capture all the PhysicalDisk counters, for example, until I learned that the ones that were the most use to me were Avg Disk Sec/Read, Avg Disk Sec/Write (to measure latency) and Disk Reads/sec, Disk Writes/sec (to measure physical IO operations, which is the measure my SAN team cares about).

Similar approach to sampling interval. Am I looking for day or week-long trends? In that case I'll only sample every 3-5 minutes, because any more often I'll be trying to get rid of data to make a usable graph. Am I looking to catch a problem the moment its happening? Then I'll sample every 15sec to 1 min.

Improve this answer
2

These might be useful links to SQL Server MVP recommendations on which Performance Counters to select:

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p1.aspx

Improve this answer
1

I'd be more concerned about the sampling interval than the number of counters; don't use 1 second if you are looking for baselines or trends; if you're running it long term you just wind up with more data that you wind up smoothing for analysis.

Improve this answer
2
  • Thanks for the tip. I've stuck with the default of 15 seconds. Commented Oct 7, 2010 at 11:52
  • Yes, this is good advice. Agreed. Commented Oct 8, 2010 at 4:55

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.