10 More Performance-Enhancing Ideas for SQL Server

Microsoft's SQL Server lets you build powerful and reliable database applications quickly, but making those applications perform their best can be tricky. Luckily, database administrators and developers can use several simple techniques to coax more speed out of a SQL Server database. In October 1996 ("10 Easy Tips for Better SQL Server Performance"), I discussed some performance-boosting tactics. Here are 10 more ideas that you can easily apply to your SQL Server database applications.

Tip 1:
Use Stored Procedures Where Possible
You can use procedural language (Transact-SQL) and SQL to create functions that are stored in the database engine, instead of in application code or libraries. Such stored procedures have several advantages. These procedures eliminate runtime parsing, because SQL parses them when you create them. You can designate certain stored procedures to run with database administrator (DBA) privileges, even if the user running them does not have this level of security. This feature lets you combine a high level of data access with tight security. With stored procedures, you can easily create libraries of functions, thereby reducing the amount of original code that your programmers must write. Stored procedures greatly reduce the amount of work needed to perform an upgrade because you can move application logic onto the server instead of distributing new versions of software to each client in an organization. When you store procedures, the SQL Server engine can read the procedures from the memory buffer instead of from the disk, thus reducing the overall amount of costly disk I/O operations. Finally, in a distributed environment, stored procedures let you cut the amount of information traveling between the front end (client) and the back end (server). This reduction can save time, especially if client and server are far apart. Another way to reduce traffic between the client and server during stored procedures is to set the NOCOUNT option. NOCOUNT disables SQL Server's DONE_IN_PROC messages, which show the number of rows a particular operation affects.

Tip 2:
Select the Best Read-Ahead Configuration Values
One SQL Server feature that can dramatically improve query performance is read-ahead, (RA, or parallel data scan). When SQL Server detects certain queries, such as table scans and other searches that return large quantities of data sequentially, it allocates a background thread to read ahead in the table. The result is that by the time your program requests this information, SQL Server might have already fetched the data into the buffer pool.

For example, suppose you're running a long report that pulls information from a large customer table. If you're reading large blocks of data sequentially, SQL Server can anticipate the next series of information that you want and read these rows into memory while you're still processing the first batch of data. This action can lead to substantial performance gains, because your program may now be able to find what it needs in memory, rather than on disk.

Let's look at how you can set the parameters listed in the SQL Server Configuration/Options dialog box to best take advantage of RA. Don't forget that changes to any RA parameters will affect database access for all SQL Server-based applications running on your system. Therefore, make changes to these parameters carefully, because an alteration can produce unintended results.

RA cache hit limit. Sometimes the RA manager attempts to locate data from the disk but finds the data still in the buffer pool. In this case, RA is not very effective. You can set the hit-limit parameter to restrict the number of buffer pool hits that an RA query encounters before the manager abandons the RA strategy. The valid range of values is between 1 and 255 hits; the default value is 4. Do not set the value excessively high; if the RA manager finds pages already in the buffer pool, the manager has no reason to continue reading ahead.

RA cache miss limit. SQL Server uses the RA cache miss limit to determine when to start reading ahead. For example, if you've set the RA cache miss limit to 5, SQL Server starts reading ahead after not finding five pages in the buffer pool. The valid range of values is between 1 and 255; the default is 3.

Setting this value too low means that SQL Server tries to read ahead on most queries; setting the value too high causes SQL Server to avoid a potentially beneficial strategy. So, if you use your system primarily for reporting and other operations that usually fetch large batches of information, set the value on the low side.

Setting it to 1, however, means that SQL Server will always issue an RA request, even when retrieving only one data page from disk. This process will negatively affect performance in most cases. This setting tells SQL Server that you usually want to start RA operations as quickly as possible. Conversely, if your system works as an OnLine Transaction Processing (OLTP) environment, with very few batch, sequential operations, raise this value because you want SQL Server to avoid RA in all but the most obvious situations.

RA delay. SQL Server uses the RA delay parameter to determine how long to wait before starting to read ahead. This value is necessary because some time always elapses between when the RA manager starts and when it can service requests. The valid range of values is between 0 and 500 milliseconds; the default is 15. The default suffices for most environments, but if you're running SQL Server on a multiprocessor machine, set it lower. Setting this parameter too high means that SQL Server can delay too long before embarking on an RA.

RA pre-fetches. You can use the RA pre-fetches measure to tell SQL Server how many extents you want it to pre-fetch during RA operations. The valid range of values is between 1 and 1000, with a default value of 3. If your applications perform primarily large sequential operations, set this value to a higher number to tell SQL Server to bring larger amounts of data into the buffer pool during each RA operation. If you set this number too high, however, you can displace other users' buffer pool pages with your data. Consequently, be careful when you experiment with this number; increase the value gradually. Try raising the value by 5 percent each time, and keep a record of overall system response between changes. Find out whether performance gains for one application degrade the performance of other applications.

RA worker threads. Threads process RA operations. The RA-slots-per-thread parameter controls the number of threads that SQL Server allocates to service RA requests. Each configured thread then supports a number of individual RA requests. The RA worker thread setting can range between 0 and 255; the default is 3. Set this option to the maximum number of concurrent users that you expect to access SQL Server. If you set this parameter too low, you might not have enough threads to service the volume of RA requests. If you set it too high, you'll start too many RA threads. SQL Server logs an error if the number of RA worker threads exceeds the number of RA slots.

RA slots per thread. The RA-slots-per-thread parameter configures the number of RA requests that each thread manages. The valid range is between 1 and 255 operations; the default is 5. If you set this value too high, SQL can overload your RA threads; the thread can spend more time switching between different RA requests than servicing the requests. A low value can lead to idle threads. Usually, the default is fine.

One final note regarding tuning RA parameters. Do not experiment with these numbers until you are comfortable with both the architecture of SQL Server and the specific performance characteristics of your system. Even when you decide to experiment, remember to change only one parameter at a time. Changing multiple parameters at once degrades performance without giving you much information about why response has become worse.