Chapter 18
Monitoring SQL Server



by Orryn Sledge

SQL Server provides several utilities that allow you to easily monitor SQL Server and its interaction with the operating system. These utilities can help a DBA quickly isolate bottlenecks and determine hardware deficiencies.

Understanding the Performance Monitor

The Performance Monitor is an excellent tool for monitoring SQL Server and the Windows NT operating system. The advantage of the Performance Monitor is that it is tightly integrated with the operating system. This enables you to track real-time statistics about SQL Server and Windows NT. You can use these statistics to isolate bottlenecks and track performance.


NOTE: The Performance Monitor may slightly degrade system performance. The overhead incurred by the Performance Monitor has been found to be 5 percent or less on single processor machines and insignificant on multiple processor machines.

Using the Performance Monitor

The following step explains how to use the Performance Monitor:

  1. Double-click the SQL Performance Monitor icon in the Microsoft SQL Server 6.5 program group. The Performance Monitor dialog box appears (see Figure 18.1).

Figure 18.1.
The Performance Monitor dialog box.

By default, the Performance Monitor for SQL Server tracks the following five predefined counters:


NOTE: The five default counters are useful but do not give a complete picture of SQL Server or Windows NT. Therefore, you will likely want to add additional counters to get a clearer understanding of system performance.

Adding Counters

Follow these steps to add counters to the Performance Monitor:

  1. From the Performance Monitor dialog box, select Add To Chart from the Edit menu.

  2. Select the object type and counter type. Click the Add button to add the counter to the chart (see Figure 18.2).


NOTE: Chart settings are valid only for the life of the chart. You must manually save the chart if you want to save your chart settings. From the File menu, select Save or Save As to save the current chart to disk.

Figure 18.2.
Adding counters to a chart.

Monitoring Key Areas

The Performance Monitor can be overwhelming because it provides so much in-formation. You can track over 70 different counters for SQL Server and hundreds of different counters for Windows NT. Trying to track this much information can drive you crazy!

Instead of trying to track everything at once, you should monitor the following five key areas (see Figure 18.3):

Figure 18.3.
Some recommended counters to watch.


These five indicators will quickly clue you in to performance bottlenecks. After you determine the general bottleneck source, you should look into the other types of counters not mentioned in this chapter (use the Windows NT Resource Guide, published by Microsoft Press, for additional information).


NOTE: When using the Performance Monitor, it is important to monitor key counters over time and establish patterns before making rash decisions. Be sure that you understand the types of operations being performed by SQL Server before adding a new processor or additional memory. A hardware improvement based on incomplete data may not yield the desired improvement in performance.

Memory

SQL Server likes memory. It uses memory to hold data and frequently accessed objects. An increase in memory may enable SQL Server to place more data into the memory cache, which may result in better performance.

When diagnosing memory bottlenecks, the following counters are useful to monitor (see "Adding Counters," earlier in this chapter for information on adding counters):

The SQLServer: Cache Hit Ratio Counter The SQLServer: cache hit ratio counter monitors the hit rate at which data was found in the data cache. If the data is not in the data cache, the server has to read the data from disk. This counter generally provides an accurate indication of memory allocation that can be used to determine whether you have sufficient memory.

A number consistently less than 85 percent may indicate that you have insufficient memory. Performance may suffer because SQL Server has to read the data from the physical disk. Reading data from the physical disk is an expensive operation. When evaluating this counter, it is important to consider the type of operation being performed on the machine and when the operation occurs. In a transaction processing environment, you can probably improve the cache-hit ratio by adding more memory. In a batch environment that uses very large databases, the cache-hit ratio may never go above 85 percent. Under this scenario, additional memory may not substantially improve performance. The Memory: Page Faults/Sec Counter The Memory: Page Faults/sec counter monitors the number of times an operating system virtual page was not found in memory. When a page is not found in memory, the operating system must retrieve the page from disk. The time it takes to retrieve a page from disk is always longer than the time required to retrieve the page from memory.

After SQL Server has stabilized, this number should remain at or near zero. If the number is consistently greater than zero, this indicates that too much memory is allocated to SQL Server and not enough memory is allocated to Windows NT. Therefore, you should reduce the amount of memory allocated to SQL Server. The Paging File: % Usage Counter The Paging File: % Usage counter monitors the percent of NT's paging file currently in use. If you find that a large percentage of the page file is in use, you may want to increase the size of the paging file to prevent an out-of-virtual-memory error. An alternative to increasing the page file is to add more memory to your server. Doing so will probably reduce page file utilization. With additional memory, you can improve performance because it is always faster to read data from memory than from the paging file.

Another point to consider when analyzing the paging file counter is a growing page file. A growing page file occurs when you specify an initial page file size smaller than the specified maximum size. From the NT operating system perspective, a growing page file is considered an expensive operation. It is better to set your initial page file size the same as your maximum page file size, which eliminates the need for the operating system to grow the page file. The SQLServer: I/O - Lazy Writes Counter The SQLServer: I/O - Lazy Writes counter monitors the number of flushed pages per second by the Lazy Writer.

A number consistently greater than zero indicates that the Lazy Writer is constantly working to flush buffers to disk. This means that the data cache is too small, which indicates that you have insufficient memory.

Processor

SQL Server is CPU intensive. Continuously high utilization rates may indicate that your CPU is the bottleneck. The best way to determine if your CPU is the bottleneck is to use the % Processor Time counter. The Processor: % Processor Time Counter The Processor: % Processor Time counter monitors the amount of time the CPU spends processing a thread.

A steady-state value above 80 to 90 percent may indicate that a CPU upgrade or additional processors may improve performance.


NOTE: Scalability (the capability to gain performance through additional processors) has been greatly improved with SQL Server version 6.x. Previous versions of SQL Server did not scale well when multiple processors were added to the computer. Benchmark tests have shown that in an online transaction processing (OLTP) environment, SQL Server 6.x scales in a relatively linear fashion for up to four processors with an out-of-the-box NT Server configuration. SQL Server can scale beyond four processors through vendor-specific extensions to the NT Server operating system.

Disk I/O

You always want to minimize disk I/O when working with SQL Server. However, when SQL Server does read and write to the hard disk, you want to ensure adequate disk performance. If you are not achieving adequate disk I/O, transaction throughput will suffer.

To help detect disk I/O bottlenecks, you should monitor the following two counters:


NOTE: You must run the diskperf command before you can monitor disk performance statistics. To enable diskperf, go to the command prompt and type the following:

diskperf -y,

You can also control diskperf through Control Panel: Devices. Then shut down and restart the computer.



CAUTION: Disk I/O values vary from one type of disk system to another. You should contact your disk manufacturer to determine acceptable counter values.

The PhysicalDisk: % Disk Time Counter The PhysicalDisk: % Disk Time counter monitors the percentage of elapsed time that the disk is busy with read/write activity.

A consistently high value (one above 2) may indicate that your disk system is a bottleneck. The PhysicalDisk: Disk Queue Length Counter The PhysicalDisk: Disk Queue Length counter monitors the number of outstanding requests on disk.

Sustained queue lengths greater than 3 may indicate a disk-related bottleneck. Also, a consistently high value for one physical disk combined with a consistently low value for your other physical disks indicates that redistributing your data may improve performance. Examine your device and segment configuration.

User Connections

How many times have you been hit with the following problem? System performance crawls during peak business hours. These are the hours when everyone in the company is banging away on the system. Transactions are being processed at a snail's pace and your phone is ringing off the hook with irate users.

I think every DBA, at one time or another, has experienced this problem. It is no secret that as the number of active users increases, the likelihood of performance degradation also increases.

To help track why and when user connection bottlenecks occur, I suggest using the following counters:

The SQLServer: User Connections Counter The SQLServer: User connections counter monitors the number of active user connections.

Use this counter to help determine when the number of active users exceeds the capabilities of your system. You may find that performance is exceptional with 50 users, adequate with 75 users, and horrendous with more than 100 users.


TIP: The SQLServer: User connections counter can also be used to help determine the appropriate configuration value for user connections. If the number of active user connections is significantly below the number of available user connections, you are probably wasting memory. Each connection uses 37K of memory, regardless of connection status.

The SQLServer: Net - Network Reads/Sec Counter The SQLServer: Net - Network Reads/sec counter monitors the number of data packets read from the network.

If you are currently not using stored procedures and you find that this counter is high and your transaction rate is low, you may be able to improve performance by implementing stored procedures. Stored procedures can help reduce the amount of network traffic. If this counter is extremely high for an extended period of time, you may be able to improve performance by using faster network interface cards (NICs).

Locks

SQL Server uses locks to ensure data consistency in a multi-user environment. You will often see various degrees of locking activity during normal processing.

Be on the lookout for blocking locks. A blocking lock is a lock that forces another process to wait until the current process is complete. When monitoring blocking, use the SQLServer-Locks: Total Blocking Locks counter. The SQLServer-Locks: Total Blocking Locks Counter The SQLServer-Locks: Total Blocking Locks counter monitors the number of all locks that are blocking other processes.

An occasional block or two is usually unavoidable. Be on the lookout for blocking levels consistently greater than zero. This usually indicates serious transaction problems. Blocking can be caused by a variety of factors. Some of the basic causes of blocking are inefficient query design, poor table design, lack of useful indexes, and slow throughput caused by inadequate hardware.

Monitoring Users

In addition to monitoring SQL Server activity, it may be useful to monitor individual user activity. By using the User Activity Monitor in the Enterprise Manager, you can view user connections, locks, process numbers, and user commands. With SQL Trace, you can monitor and record Transact SQL activity and other statistics.

The User Activity Monitor

One of the best features of the User Activity Monitor is its capability to view more information about a process. This is a major plus for DBAs: It gives you all the information you need to know about a process. You can use this information to help kill a process or to pinpoint a query that is a burden to the system.

Follow these steps to use the User Activity Monitor:

  1. Click the Current Activity toolbar button from the Enterprise Manager window. The Current Activity dialog box appears (see Figure 18.4).

  2. From the Current Activity dialog box, you can view three types of infor-mation: User Activity, Detail Activity, and Objects Locks. Click the corresponding tab in the Current Activity dialog box to view each type of information (see Figures 18.4, 18.5, and 18.6).

    Figure 18.4.
    The Current Activity dialog box, showing the User Activity page.


    Figure 18.5.
    The Current Activity dialog box, showing the Detail Activity page.



    NOTE: The information displayed in the Current Activity dialog box must be manually refreshed. To perform a refresh, click the Refresh toolbar button in the Current Activity dialog box. When you kill a process, you will want to verify that the process was terminated by clicking the Refresh toolbar button and then reviewing the process information.



    TIP: Click the Display Legend toolbar button to display a legend for activity information (see Figure 18.7).


    Figure 18.6.
    The Current Activity dialog box, showing the Object Locks page.



    Figure 18.7.
    The Activity Legend menu.



    TIP: Be on the lookout for blocking when users are complaining that their transactions are hung. Select the Detail Activity tab in the Current Activity dialog box to view blocked processes.

  3. From the Current Activity dialog box, double-click a process to view more information about the process. The Process Details dialog box appears (see Figure 18.8). From this dialog box, you can view the last statement issued by the user, kill a process, and send a message to the user.

Figure 18.8.
The Process Details dialog box.


NOTE: The send message feature is available only for Microsoft networks.


TIP: The Transact SQL commands sp_who, sp_lock, KILL, DBCC INPUTBUFFER, and OUTPUTBUFFER can be used to perform functions similar to the functions found in the User Activity Monitor.

Using SQL Trace

SQL Trace is a utility included with SQL Server version 6.5 that allows you to monitor Transact SQL activity for a particular server. Using this utility, you can investigate user activity and generate audit trails. This utility is also useful for observing SQL statements generated by applications and end users. In turn, this information can be used to pinpoint poorly constructed queries or security problems. Another feature of SQL Trace is its capability to save the trace information to a file. This feature allows an administrator or developer to replay the queries from the trace session by cutting and pasting the information into separate query sessions. As you can see, this information can be beneficial when trying to tune queries and stored procedures.

Follow these steps to use SQL Trace:

  1. Double-click the SQL Trace icon in the Microsoft SQL Server 6.5 (Common) program group. This starts the SQL Trace utility. The Connect Server dialog box appears (see Figure 18.9).

    Figure 18.9.
    The Connect Server dialog box.

  2. In the Connect Server dialog box, enter the server name and login information. Click the Connect button to initiate a trace session.

  3. If this is the first time you have used SQL Trace, you are prompted to create a new filter. Click the Yes button when prompted. This opens the New Filter dialog box (see Figure 18.10).

  4. From the New Filter dialog box, you can create a filter to trace SQL statements. When you create a filter, you can specify the types of information to trace such as Login Name, Host Name, and so on. After entering the filter information in the New Filter dialog box, click the Add button to save the filter. Clicking the Add button also activates the filter you just created (see Figure 18.11).

    Figure 18.10.
    The New Filter dialog box.


    Figure 18.11.
    An active filter example.

  5. When the filter is active, it displays the SQL statements that match your trace criteria. As you can see, the ability to trace SQL statements provides a wealth of information that can be used to analyze SQL activity.


NOTE: The best method I've found for tracing activity is to create a filter that tightly defines the type of information you are looking for. This eliminates a lot of extraneous information that may not be of interest to you. For example, if you are only interested in tracing the activity for a particular user, create a filter that monitors activity for only that particular user. When you create a filter, you can specify that the trace information be saved to a log file or to a SQL script. The difference between a log file and a SQL script is the following:


Between the Lines

Following are some important notes to remember when monitoring SQL Server:

Summary

The Performance Monitor and User Activity Monitor are two valuable tools for tracking server utilization and pinpointing bottlenecks. All DBAs should keep an eye on their system by tracking a few key counters. The next chapter discusses server configuration and tuning.



DISCLAIMER


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select
Talk to Us.

© 1997, QUE Corporation, an imprint of
Macmillan Publishing USA, a Simon and Schuster Company.