1

I have collected perfon data but to analyse I need to know the threshold values for all counters. Especially counters under Memory, Physical disk and processor object. I have gone trhough many sites which explain few counters usually required to monitor SQL performance issues and some have mentioned their threshols too but they have not encluded entire counter list. However if windows has provided so many counters then I am assuming that there must be some purpose behind it and I would like to know corresponding thresholds so that looking at any counte value I can conclude that it is a bottleneck or not. Counters I am ineterested in:

PhysicalDisk: %Idle Time, Avg. Idle Time, Avg. Disk Bytes/Read, Avg. Disk Bytes/Transfer, Avg. Disk Bytes/Write, Disk Bytes/Sec, Disk Read Bytes/Sec, Disk Write Bytes/Sec, Split IO/Sec,

Memory Object: %Commited Bytes In Use, Cache Bytes, Cache Bytes Peak, Cache Faults/Sec, Commit Limit, Commited Bytes, Demand Zero Faults/Sec, Free & Zero Page List Bytes, Modified Page List Bytes, Page Faults/Sec, Page Output/Sec, Pool Nonpaged Allocs, Pool Nonpaged Bytes, Pool Paged Allocs, Pool Paged Bytes, Pool Paged Resident Bytes, Standby Cache Core Bytes, Standby Cache Normal Priority Bytes, Standby Cache Reserve Bytes, System Cache Resident Bytes, System Code Total Bytes, System Driver Resident Bytes, System Driver Total Bytes, Transition Faults/Sec, Transition Pages RePurposed/Sec, Write Copies/Sec,

Processor object: %C1 Time, %C2 Time, %C3 Time, %DPC Time, %Idle Time, %Interrupt Time, %User Time, C1 Transitions/Sec, C2 Transitions/Sec, C3 Transitions/Sec, DPC Rate, DPCs Queued/Sec, Interrupts/Sec,

Thanks!

3
  • "Complete"? for what, just the base OS + SQL or other applications? if only those then what versions? you've not made it easy for us to answer this one.
    – Chopper3
    May 16, 2011 at 11:35
  • Even if you've got the complete list, no one can tell you what threshold values you should care about. That's specific to your environment, your servers, your applications.
    – mfinni
    May 16, 2011 at 12:52
  • Apologies for not clarifying doubt well. I am doing performance troubleshooting on SQL server 2000, 2005, 2008 on windows server 2003 and 2008. For now I am focusiing on three performance objects: PhysicalDisk, Memory, Processor. However I was unable to get clear idea on some of the counters(listed in origional post) which come under these three headers. Ofcourse I agree with you guys that they are enviroment dependent but I was looking for sample examples. Anyway I think I understood that it is practically impossible to provide exact samples which can be generalised for all environments. May 17, 2011 at 7:15

2 Answers 2

1

There are no "hard" limits that can apply to all environments, it generally depends on your installation and requirements, as well as your hardware.

Having said that, there are definitely general guidelines that you can follow:

http://www.extremeexperts.com/sql/articles/SQLCounters.aspx

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

I would also check our SQL Server Central (http://www.sqlservercentral.com), which has useful resources on SQL Server. The sites requires a (free) account however.

To come up with "thresholds", you should consider creating a baseline of your performance counters and then set thresholds based on the baseline values you observed over a given time period.

You can use software like EventSentry to capture performance data in a database over time, and setup threshold alerts.

1

Coming to the values you should monitor for the Perfmon counters there is no such comprehensive list as the best numbers depend on a lot of factors and the closest and a good one I have seen is

http://grumpyolddba.co.uk/monitoring/Performance%20Counter%20Guidance%20-%20SQL%20Server.htm

http://grumpyolddba.co.uk/monitoring/Performance%20Counter%20Guidance%20-%20Windows%20Server.htm

HTH

1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .