SQL Server Performance Monitoring for Windows Administrators

Looking at the Big Picture of SQL Server Performance

Last month we took a look at the big picture of Linux server performance monitoring – what to monitor, how often, and basic rules of thumb. This month we jump to SQL Server and discuss key SQL Server performance metrics that anyone new to SQL Server administration will want to know about. Here’s what we recommend you look at when investigating performance issues on SQL Servers:

Memory Available to SQL Server

If the percentage of free memory available to be used by SQL Server is consistently low - less than 20% of the memory available to SQL Server is free – it will significantly degrade the performance of SQL Server.

SQL Server gives you several options for allocating memory to SQL Server. You can configure SQL Server to use a fixed amount of the memory available on the server, you can configure SQL Server to dynamically allocate memory to itself according to demand, or you can configure SQL Server to dynamically allocate memory to itself within limits that you configure.

If adjusting how memory is allocated to SQL Server does not free up enough memory for the SQL Server instance to function properly, the physical memory for the server may need to be increased, or the concurrent application load on the server may need to be decreased.

CPU Utilization

Consistently high CPU utilization rates, over 90%, may indicate a poorly-tuned or designed application, or an underpowered system.

A good auxiliary metric to look at is the size of the processor queue. If you see high CPU usage and a processor queue that exceeds two over time, it means that processes consistently have to wait for the CPU – a sure sign of a bottleneck.

If that is the case, check for blocked SQL processes or processes using large amounts of CPU time.

Disk I/O

High disk utilization, over 50%, results in sluggish response time and poor application performance. Performance can particularly degrade if the disk queue length exceeds two. Disk queue length is the number of outstanding requests that need to be serviced by the disk.

If this situation persists, either reduce the concurrent application load or upgrade the computer with faster disks and/or more disks. Faster disks will cope with the I/O load better. Additional disks will allow for the I/O load to be spread across more spindles.

Response Time for Commonly Run Queries

Monitoring the response time of commonly run queries over time is an effective way of gauging the relative performance of SQL Server. If the response time for the same queries increases to unacceptable levels, the resources on the server may need to be increased.

You can use the Longitude Transaction application to execute queries against a SQL Server.  You can also configure a Longitude SLA to make sure that the response time of the query remains acceptable over time.

SQL Error Logs

In addition to performance metrics, it is a good idea to monitor the SQL Error logs for the presence of errors that could affect the performance of the server. Some errors to watch for include:

Error 1204	Not enough locks
Error 1205	Deadlock victim
Error 1105	Can’t allocate space for object
Error 17809	Unable to connect
Error 605	Database corrupted

If you experience any of these errors, Heroix Longitude will alert you to potential consequences and advise on how to respond to them.

As Always: Your Mileage May Vary

In any performance monitoring exercise, remember that these are rules of thumb, and you will need to make adjustments based on the characteristics of your various expected workloads; however, if you monitor these metrics consistently, you will develop a clear picture of exactly what constitutes normal behavior on your system, and you will be better equipped to detect and deal with the subtle symptoms that spell trouble ahead.

Next month: Tips for SLA Monitoring