Using Performance Monitor
April 6, 1999
Note: This article assumes knowledge of how to use and interpret Performance Monitor output.
Its important to remember that you need to use these counters in conjunction with each other to get any sensible results. To change SQL Server configuration options use the sp_configure stored procedure.
There are a number of areas that Performance Monitor can help diagnose, these include:
Processor: % Processor Time
You need to determine if SQL Server is the cause of this high usage, and if it is SQL Server which spid is causing the trouble. Using SQLServer-Users: CPUtime will give you some idea. Once you know which process is causing the trouble you can examine the query design, index usage and database design. The query may well be getting high cache-hit ratios but still require excessive I/O, which eats CPU cycles, look at the table design and index usage. Take a look at using Processor: % Privileged Time below. If this doesn't help then try adding faster or more processors to your server. If your system has enough memory to run SQL Server, adding processor power is the best way of increasing overall performance.
Odd as it may seem very low CPU usage is also worth watching for. Although this may seem a little odd low usage can be more of a problem than high usage. Low usage can result from locking contention or your application stalling. The bottleneck in this case is probably your application and not the processor!
Processor: % Privileged Time
System: Processor Queue
System: Context Switches/sec
Process counters are mostly run on the SQL Server process instance, although it can be useful to run some of them against other problem applications.
Process: % Processor Time
Process: Thread Count
Process: Virtual Bytes
Process: Working Set
Process: Page Faults/sec
SQLServer: Cache Hit Ratio
SQLServer: I/O - Page Reads/sec
SQLServer: I/O - Batch Writes/sec
SQLServer: I/O - Transactions/sec
Memory TuningAs there is no soft page faults counter use the following to calculate the number of soft page faults.
Memory: Page Faults/sec - Memory: Page Input/sec = Soft Page Faults per sec
Memory: Pages/sec / Memory: Page Faults/sec
Disk Subsystem TuningWhen performance tuning the disk subsystem, you should attempt to isolate the disk I/O bottleneck with the SQLServer counters first, using the PhysicalDisk and LogicalDisk counters for more detailed monitoring. The logical counters monitor the logical NT drives (i.e. the drive letters) while physical counters monitor what disk administrator sees as a sngle physical device.
To be able to examine low-level disk activity it is necassary to run the diskperf -y command. This does however produce a load on the system and it is important to remember to turn it off using the diskperf -n command.
PhysicalDisk: Disk Queue Length
As you can see, optimisation is very much a work of art, it involves alot of trial and error. I hope this aricle will give you a good introduction as to what to look for and some of the more important counters.Copyright ) 1998-99 G.h.van den Berg. All rights reserved.
This article may not be resold or redistributed without prior written permission from Guy van den Berg