SQL 2000: Performance Monitor Counters - Advice Please

[fuzzyocelot]

Hi everyone again!

We're having some performance issues with another server. This one is running SQL 2000 Standard (SP3) on Windows Server 2003 SP2. It has 1.25 GB of RAM.

I ran some performance counters on the server. I set the following:

Memory:
-Page faults/sec
-Pages/sec

Physical Disk:
-%Disk Read Time
-%Disk Write Time
-Avg Disk sec/Read
-Avg Disk sec/Write

Processor:
-%ProcessorTime
-Interrupts/sec

SQLServer Buffer Manager:
-Page life expectancy

Please let me know if I need to run other counters. I also set it to log the results every 1 minute.

I ran the counters between 12:30 pm and 4:00 pm. The results showed 8 spikes in the page faults/sec. The "normal" value is around 16-100. These spikes were over 1000. At the same time of these spikes, the %ProcessorTime spiked to around 70-99%. The normal value was around 1-3%. This makes me wonder if there's something the users are running every so often that is causing a problem. My SQL traces didn't have much info in them. I put the filters on too tight. I'll re-run SQL traces tomorrow with new performance counters to see if I can find any queries that correlate with these spikes.

What was interesting is that the Interrupts/sec was over 1000 the entire afternoon. This is leading me to wonder if there is a hardware or driver problem as well.

The page life exepectancy values were huge! They ranged from 97,780 to 752,359. It increased throughout the day. Does this mean something good or bad? I figured it was good because it's over 300.

Do I need to run additional counters? What would be good trace events to run?

Thanks!!!!

 

[Ovatvvon]

Here's what I usually run:

Processor
CPU % Time
On stand-alone boxes, not sustained above 60% (although spikes are ok). Sustainment levels would be valid if they remained for 15 minutes or more. On Clustered boxes, not sustained above 40% (to allow enough head-room in case one cluster instance fails over and both instances are running on a single machine). If sustained levels are at or above those, you probably want to add more / faster CPU's.


Memory
SQL Server: Buffer Manager: Buffer Cache Hit Ratio
For SQL Servers, if this is 90% or lower, then you need to add more memory. Most of our servers run between 98-99%.

Memory: Page Faults /sec and
Memory: Page Inputs /sec
Use these to calculate the percentage of Pages Faults that result in Hard Page Faults (requiring the system to go to the physical disk, rather than memory). Do so by Dividing the Page Inputs/sec by the Page Faults/sec. A value of 20% or higher means you need more memory. If you are close to 20%, you may want to think about it anyway.


Physical Disks
Physical Disk: Average Disk Queue Length
This is how many items are queued up, waiting to be services. (Standing in line, waiting for the disks to be able to process them.) Rule of thumb is, sustained values (average values) above a value of 2, means you need more disks in the array, or faster drives (or both). Or may need to think about alternative solutions, such as splitting your MDF and LDF files to separate drives / arrays, and even TempDB files to separate drives / arrays (that is, if you do not already do that).


hope this helps










-Ovatvvon :-Q

 

[fuzzyocelot]

Thanks! I'll give it a shot!