Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
"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 '11 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 '11 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. –  Dnyaneshwar Daspute May 17 '11 at 7:15
add comment

2 Answers

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

share|improve this answer
    
Thanks Sankar. This is helpful. –  Dnyaneshwar Daspute May 17 '11 at 7:19
    
Please find the post of schuhtl at below link. Seems good:sqlteam.com/forums/topic.asp?TOPIC_ID=64044 –  Dnyaneshwar Daspute May 19 '11 at 6:45
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.