Performance Counters

[sajuks]

Dear Folks,

 Need some help in understanding which performance counters to monitor and set when using sql server 2000 with windows 2003. What threshold do i set?

Thanks
Saju

[Kevin3NF]

Profiler or Perfmon?  What to set generally depends on what you are trying to troubleshoot.  Most installations don't run anything on a system where there are no performance issues.

[sajuks]

Perfmon.
 Need to take some statistical data/presentation to provide a system usage high level report.

[Kevin3NF]

BufferManager:BufferCacheHitRatio - should be in the mid to high 90s - lower numbers indicate memory pressure
BufferManager:PageLifeExpectancy - larger is better...this is how many seconds a page stays in memory before being sent back to disk
GeneralStatistics:UserConnections - obvious

There are a ton of SQL Specific perf counters, but those are the three I hit most.  After that, I go for the O/S level counters, such a CPU utilization, Avg Disk queue length, etc.