Experts Round Table Network

Databases => MS SQL Server => Topic started by: sajuks on February 24, 2007, 07:34:03 PM

Title: Performance Counters
Post by: sajuks on February 24, 2007, 07:34:03 PM

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

Title: Re: Performance Counters
Post by: Kevin3NF on February 26, 2007, 06:07:30 AM

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.

Title: Re: Performance Counters
Post by: sajuks on February 26, 2007, 09:08:17 AM

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

Title: Re: Performance Counters
Post by: Kevin3NF on February 27, 2007, 07:51:23 AM

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.