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!
Best Answer
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.