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.
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.
The following step explains how to use the Performance Monitor:
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.
Follow these steps to add counters to the Performance Monitor:
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.
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.
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.
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.
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.
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).
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.
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.
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:
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.
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.
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:
Figure 18.9.
The Connect Server dialog box.
Figure 18.10.
The New Filter dialog box.
Figure 18.11.
An active filter example.
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:
Following are some important notes to remember when monitoring SQL Server:
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.
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.