Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Performance Monitoring Tools: Windows System Monitor

Last updated Mar 28, 2003.

Before you can make changes to the system you're working on to improve performance, you need a method to track and record changes in the objects involved. This is part of the second phase of the performance tuning process, where you evaluate the system's components.

There are two times when you need to monitor your systems. The first is when the system is first installed, or if that isn't possible during a time when the system is performing acceptably. This measurement is called a "baseline". It's the point to which you'll compare your measurements when the system isn't performing as well, which is the second time you will need to monitor your system.

You should also measure the system on a periodic basis so that you know when something has changed and could potentially become an issue. Monitoring also helps you prevent performance problems, alerting you to a bottleneck before the users feel it.

There are several tools you can use to monitor SQL Server, including SQL Profiler, various Transact-SQL statements, and even third-party tools such as Quest Software’s Spotlight on SQL Server. The tool used most often, however, is the Windows System Monitor. This tool is often referred to as the Performance Monitor, since that's what it was called in older versions of Microsoft operating systems.

The System Monitor is actually a reader for "hooks" built into various Windows programs. These hooks publish various parts of a program, called objects, and the things those objects do, called counters. Using System Monitor, you can watch those counters in real time, or even capture them to a log for later examination. System Monitor also contains a facility to watch objects and counters and take an action based on a value you specify.

The System Monitor works in an interesting way. It reads several specialized registry entries, usually once a second. Applications can write values to these registry entries, which then show up in the tool. Each item that writes a value is called an object, and within the objects are counters. For instance, the CPU is an object, and it has several counters, one of which is called % Utilization. The value for that counter represents the current percentage of the processor that is in use. In addition to objects and counters, System Monitor also has instances. You may have several CPU's in a server, and each one reports values independently. Each CPU, in that case, is an instance. This has great bearing on systems such as SQL Server, since it can parallelize queries across multiple processors. By watching their percentage of utilization during a query run that you think should be parallelizing, you can determine what is happening.

It’s important to be able to use this tool effectively. While you should know how to use the tool, it’s equally important to know why to use it, when to use it, and what to use it for. Before you begin monitoring, you should be familiar with general computer architecture, how Windows uses that architecture, and what the developer that wrote the objects and counters intended to show before you take a stand on the results of the metrics.

It might seem that this tool would solve all of your performance tuning issues. While there are times when you can definitively say "if this object’s counter is X, then Y is the issue", it’s usually not so. Even when it’s usually true, there are exceptions. There is simply no substitute for understanding the theory behind the number you are looking at. You'll need to evaluate several objects and counters to zero in on a problem.

Let's take a look at the tool. From the Administrative Tools menu item in the Windows Start button on your test server, select the Performance item. Once the tool is opened, you’re placed in the Current Activity view. Depending on your operating system, a few counters may already be loaded. Clicking the plus sign in the tool bar brings up a panel that allows you to set the system you wish to monitor, and shows the objects that you can select from. The objects are dependent on the software and patches installed on the system you’re monitoring.

Once you’ve selected an object, you can select the counters that are associated with that object. There are two variables per counter. One is that you can select all counters per object. Unless you really need all counters within an object, it’s usually best not to use this feature. The other option is the instances I mentioned earlier. You can monitor the counters for each of these objects separately or collectively by making a selection in this option. As I mentioned before, it’s important to understand the object you’re looking at to determine which option to choose.

As a general operating procedure, I usually suggest that you add several objects, and then remove them as they conform to expected values. You can then "drill in" to the objects and counters that deviate the most from your earlier baseline. I also recommend the default time sample; unless you have a specific need to see a more granular collection or you need to collect for a longer period of time. Again, the object and counter will dictate the collection span used to monitor it. For instance, if you wish to monitor an item that potentially peaks every 10 seconds, setting the interval of 15 seconds may cause you to miss it.

Once you’ve added the objects and counters you care about, select Close and you’ll see the numbers moving across the screen. This view is useful for evaluating trend lines. You can right-click within the screen and set options such as update frequency, grids and so forth to make the view more relevant to what you’re watching. You can change the view to a histogram, often useful in evaluating "slice of time" items, such as hard drive space usage.

What you're looking at is the current activity of the system. The current activity view is used to watch the server, but it is only as historical as the time spanned on the screen, and only that portion can be saved off for later review. For historical monitoring, double-click the Performance Logs and Alerts item on the left of the tool, and then click the Counter Logs item. Right click in the right-hand panel to create a new log. This is where you collect the objects and their counters to a file. This file can be an ASCII file or a binary file. In Windows 2003, you can also save these logs to a SQL Server database. Usually the best option is the binary file, since it takes less of a performance hit than the ASCII file, even though the ASCII model makes for smaller files.

Once you add the objects and counters to the log, it needs to be started. You can start the gathering process manually or at a certain time, and you can stop it the same ways. The option to do that is on the Schedule tab. If you choose to start the logging manually, then once you save the log you can right-click the icon for the log and start or stop it there.

Once the log file is created, you examine it by opening the chart view and clicking the icon that looks like a database or drive. Selecting the file from this option drops you back into a blank palette. Now that the log file is loaded, you can access the objects and counters in it, just as if you were doing this in real time.

Click the plus sign on the tool bar to bring up the objects and counters that were logged. When you select them, their entire results are presented on the screen, rather than a slow crawl. Since it’s difficult to evaluate the data this way, I normally right-click in those results, save them off as a comma-separated value ASCII file, and then open it in Microsoft Excel. I can then use statistical methods to slice and dice the results and find what I’m looking for.

In addition to the Chart view and the Counter Log settings, note that there is also an option to create a Trace Log. This type of log makes entries only when certain activities occur, such as a page fault.

The final option in System Monitor is create an Alert, which can watch an object’s counter and take an action based on the values you specify. The option to be careful about here is the number of times the action will occur. If you set a threshold of 3 for a counter, then each time that value (per sample) exceeds three you’re server will take the action. So if you’re sampling ever second and the counter stays at 3 for 10 seconds, your server will take 10 actions. To avoid this behavior, set the action to happen only on the first occurrence. Of course, that means that the next time the counter hits 3 your action won’t fire.

Now that you have a grasp on how and when the tool should be used, what objects and counters are useful to know about monitoring your SQL Server installation? I’ll present a few here, along with comments, but again I want to warn you away from blindly applying these counters with their values to your system. By all means, monitor your system, but also make sure that you spend some time researching these items to know if your values have the same interpretation.

The other caveat is that your system may not have all these counters, or they may be named differently than what you see here. Each software release and service pack can publish its own objects and counters, so what you see here is available on my system as of this writing.

I used these counters to evaluate the performance of a system using SQL Server as a back-end to an IIS application. Obviously, other applications will require different objects and counters to be monitored. These objects and counters serve as a demonstration of the types of things I monitor for a SQL Server application I use:

Object

Counter

Meaning

Notes

Cache

Data Maps Hits %

NTFS Processing Efficiency

Should be better than 90% or may need to defrag Hard Drive

Cache

MDL Read Hits %

Cache Hits for IIS

Should be better than 90%

Memory

% Committed Bytes

Shows memory use

Should be under 70

Memory

Available MBytes

How much unallocated RAM is left on the server

Should be greater than 50MB

Memory

Cache Faults/Sec

Physical Disk Read I/O Operation

The fewer the better

Network Interface

Bytes Total/sec

Shows network activity

Network Monitor Driver must be installed for accurate measurement of network items - Measure against network bandwidth availability

Network Segment

% Network Utilization

Total Network segment use - not from this server alone

Depends on many networking variables, but can be useful to a Network specialist

PhysicalDisk

Current Disk Queue Length:_Total

Shows physical drive activity

Less than 3 per physical disk is acceptable

PhysicalDisk

% Disk Read Time

How much time spent doing reads

Combine with Writes to see if Index usage is correct - may need to adjust fillfactor

PhysicalDisk

% Disk Time -- _Total

Shows drive activity

diskperf -yv is required for proper logical disk counters to operate. Should be less than 55% - watch for increase

PhysicalDisk

% Disk Write Time

How much time spent doing writes

Combine with Reads to see if Index usage is correct - may need to adjust fillfactor

Process

% Processor Time

Pick Specific Object

Will explain how much that object is taking on the processor

Processor

% Total Processor Time

Shows the CPU activity being taken by all processes

Should not exceed 80% for continuous periods with high Proc Queue Length. NOTE: W2K measures non-busy time and subtracts it from 100%

Server

Bytes Received/Sec

Data received by the server NIC

 

Server

Bytes Transmitted/Sec

Data sent by the server NIC

 

SQL Server:Access Methods

Full Scans/Sec

Table scans

For the entire server - not just one database

SQL Server:Access Methods

Page Splits/Sec

Splits happen when data or indexes span a page (8k)

Fillfactors may not be correct if this is high

SQL Server:Buffer Manager

Cache Size (pages)

 

Multiply x 8192 for RAM amount, should be close to the RAM in your system.

SQL Server:Locks

Average Wait Time (ms)

Time processes spend waiting on a lock release

Should not be high

SQL Server:Locks

Number of Deadlocks

Number of processes deadlocking each other

Measurement is by the second

SQL Server:Memory Manager

Target Server Memory

How much RAM SQL wants

 

SQL Server:Memory Manager

Total Server Memory

How much RAM SQL is using

 

SQL Server:SQL Statistics

Batch Requests/Sec

Bow many batches sent to SQL Server

Over 1000 indicates a busy SQL Server - May indicate CPU bottleneck. A 100Mb network card can handle 3000 per second.

SQL Server:SQl Statistics

SQL Compilations/Sec

How many compiles SQL has to do

Over 100 may indicate a SQL problem

SQLServer:Buffer Manager

Buffer Cache Hit Ratio

Shows how much data is found in the Buffer

Should be 99% or greater

SQLServer:General Statistics

User Connections

Shows the number of connections taken on the SQL Server

Varies

System

% Total Privileged Time

Kernel mode operations

Should be less than 20% or may be IO bound. Pair with %Disk time counter to see if greater than 50%. Can also indicate driver or NIC.

System

Context Switches/Sec

Server thread switches

Should not exceed 8000 per second, per processor

System

Processor Queue Length

Shows the amount of processes waiting for CPU time

Pairs with % Processor Time counter - Should not be greater than 2 per proc.

Web Service

GET Requests/Sec

Number of GET requests

Each GET connection attempt

Web Service

POST Requests/Sec

Number of POST requests

Each POST connection attempt

Web Service

Total Method Requests/Sec

Hits on the Web Server

Service Request rate

These counters, along with others, can be used to help you understand what your system is doing. In another tutorial I show you how SQL Server provides user-defined counters (10 of them) that you can update from your SQL code. Using these counters, your application can report its status to the System Monitor.

Informit Articles and Sample Chapters

Along with the other performance tuning tutorials I've got in this section, other resources exist on Informit to help you tune your system. Here's an article by Kevin Kline, Baya Pavliashvili called Application Performance Tuning.

Online Resources

Intel has a great article that talks about monitoring the processor using Performance Monitor.