SQL Server Reference Guide

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:

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.