Performance-Tuning SQL Server & Identifying Bottlenecks

Leave a comment (0) Go to comments

Database server performance becomes increasingly important as time goes on and businesses grow, making databases larger and busier. For these reasons and others, doing a performance checkup and tuning database servers is essential to the organization’s operations.

Using performance counters is the best way to check your SQL server. By understanding each of these counters, you can focus on what really needs to be done to make immediate and necessary performance enhancements. SQL Server 2000 includes many useful counters for looking at access methods, buffering, databases, caching, memory, backup, replication, and more. Knowing which counters to look at is important for determining how SQL Server is taxing system resources. In this article, we’ll take a look at some examples. All counters can be viewed using the Performance Monitor snap-in, which is located in Administrative Tools.

SQL Server 2000 includes many useful counters for looking at access methods, buffering, databases, caching, memory, backup, replication, and more. Knowing which counters to look at is important for determining how SQL Server is taxing system resources. Jesse Smith helps you find out whether your SQL server is under stress or performing exceptionally.

Analyzing Memory Usage

Memory is so important to SQL Server that not having enough will really strain your hard drives. Basically, anything SQL Server can’t commit to memory for processing will be forwarded to the disk subsystem—for recording upcoming queries that need to be executed, and so on. Processing instructions from memory is much quicker than getting these instructions from the hard drive (virtual memory) and executing them. The goal is adequate memory and cache to handle everything your system needs. These counters provide clues for making sure that memory on your SQL server is sufficient:

  • SQL Server: Memory Manager: Memory Grants Pending. A memory grant allocates SQL Server a workspace in memory to perform queries. If this counter value is high, your SQL server is memory-constrained. A high value indicates that too many memory grants are in a queue, waiting to be carried out.

Often, large queries being executed by concurrent users will cause poor memory performance. Reducing the value of this counter increases memory performance. Look at improving your query performance, especially by converting hash-join queries to inner loop queries.

  • SQL Server: Memory Manager: Total Server Memory. This counter indicates how much dynamic memory SQL Server is consuming. The whole point of dynamic memory allocation is to give and take as needed; SQL Server is good at increasing dynamic memory usage, but not so good at decreasing it!

A good way to check whether this value becomes unusually high (and too quickly) is by stopping and restarting the SQL service. Record the initial value after restart, and then check it again after a couple of days. If the second value is significantly higher—say, more than double the initial value—SQL Server’s dynamic memory-management process isn’t giving memory back to the operating system as efficiently as it should. There may be a number of causes, such as not closing connections, or closing and destroying recordset objects in your web applications’ ASP/ASP.NET code using SQL Server. Until the service is restarted, dynamic memory is still being allocated to these connections and objects that are no longer used. Each time an application with poor coding practices is compiled and executed against a database, dynamic memory allocation literally becomes exponential.


A good coding practice to avoid consuming unnecessary memory is to keep queries out of applications’ control loops.

  • SQL Server: Cache Manager: Cache Hit Ratio. If this value is consistently lower than 80%, your system isn’t using enough memory to fulfill cache requests for good performance, but rather is using the disk subsystem. Try allocating more memory to SQL Server or increasing system memory.
  • Memory: Page Reads/Sec. This counter can tell you whether the system has an adequate amount of memory for SQL Server. Paging occurs when Windows has to use the hard disk (virtual memory) because no system RAM is available. If Windows is reading more than five pages per second from the paging file, that’s excessive paging; you don’t have enough system RAM for good SQL Server performance. Removing any unnecessary applications from the server, disabling any unnecessary services, and adding RAM should bring paging to a reasonable level.

Analyzing Processor Usage

Processor usage is very important to SQL Server because the more processor time available to execute instructions at any given time, the faster SQL Server can perform necessary tasks. Several factors can affect processor usage, from poorly designed databases and queries to increased disk usage. The following counters are good for checking up on the processor(s):

  • System: Processor Queue Length. This counter is good for clueing into processor strain. To come up with a good queue number for your benchmark, multiple the number of processors on your server by two. For example, if your server has two processors, a good queue length should not exceed four; if your processor queue length is consistently higher than four on a system with two processors, your CPU is bottlenecking, causing increased query-execution time. To remedy this problem, you’ll have to tune queries, reduce paging, or come up with better indexes. If these steps don’t improve queue length, it may be time to add another processor system.
  • System: Context Switches/Sec. SQL Server uses threads to execute batches of SQL statements from clients. If this value is high, SQL Server and Windows are switching many times per second from executing on one thread to executing on another, increasing CPU time and bogging down the system. If this value is high, it usually also means that the queue length (see bullet above) is also high. To optimize threading and increase processor performance, try setting SQL Server to use lightweight pooling. Doing so will make SQL Server use fibers instead of threads. Fibers use fewer resources; when pooled, they also allow SQL Server to optimize processing time when executing SQL statements concurrently. Lightweight pooling is an advanced option and can be set to 1 (to turn it on) using the sp_configure system-stored procedure.
  • Processor: %User Time and Processor: %Privileged Time. Viewing these two counters together is good for indicating unnecessary processor strain by handling excessive I/O requests to the disk subsystem. If the %Privileged Time is consistently over 20% and %User Time is consistently below 80%, you have excessive I/O requests. Check your disk counters to confirm this problem, and get your I/O requests to the disk subsystem down to a reasonable level (see the next section).

Analyzing Disk Usage

Increased disk time on your database server usually means that your system is using more virtual memory due to a lack of RAM (analyze memory issues as described earlier). Monitor these counters:

  • Physical Disk: Avg. Disk Queue Length. This counter indicates how busy a drive is becoming due to excessive I/O requests. If one drive is getting much more activity than another, try moving some SQL Server files from the busy drive(s) to other drives that are not so busy. This technique will help to spread the disk I/O activity and reduce bottlenecking on one drive.


A good practice for increasing and optimizing disk I/O performance is to keep all database transaction log files on a hard drive separate from the database files. Because transaction logs are written to sequentially, keeping them on separate hard drives allows for a significant I/O performance boost.

If memory is good and the above strategies still don’t decrease I/O, look at your current disk configuration for ways to increase disk I/O performance. A good configuration for performance is multiple SCSI drives using a RAID (redundant array of inexpensive disks) configuration (preferably 1 or 5). A 32-bit SCSI controller will transfer larger chunks of data faster, thus decreasing I/O time. A RAID configuration allows for concurrent asynchronous I/O requests, speeding up disk performance immensely.

  • SQL Server: Databases: Log Flush Wait Time and SQL Server: Databases: Log Flush Waits/Sec. Disk I/O performance becomes especially important when SQL Server writes a transaction to the transaction log on the hard drive. The busier your databases are, the more disk I/O time will increase. These two counters tell you whether your disk I/O performance is sufficient for handling transaction log requests. The greater the wait time, the longer SQL Server is waiting to write the next transaction to the disk subsystem. As your server gets busier, these values increase because there are more sequential writes to the log. Make sure that I/O performance is sufficient by keeping these numbers reasonable under peak conditions.


This article described how to look for performance bottlenecks that may be occurring on your SQL server with memory, processors, or your server’s disk subsystem. Identifying these bottlenecks is only half the battle, of course; knowing how to increase performance based on what you find is what really counts. From optimizing queries to choosing a better disk configuration, there are many ways to increase performance. If you’re an administrator looking at purchasing and setting up a new SQL server, learning and practicing these strategies can help prevent bottlenecks from occurring.

*Source : Jesse Smith

*** Maily applyies to SQL Server 2000

EOF - Performance-Tuning SQL Server & Identifying Bottlenecks, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.