COAX EVEN MORE SPEEDOUT OF YOUR SQL SERVER DATABASES
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.