6. Wasted Network Bandwidth
SQL Server’s default behavior
is to constantly
update clients about
current execution status
while a query, batch, or
operation is being processed.
Although beneficial
in some cases, this
chattiness adds additional
network overhead.
Therefore a good rule of
thumb is to ensure that
stored procedures called
by applications take
advantage of the SET
NOCOUNT ON directive
to turn off status
messages. Doing so can
cut back significantly on
network traffic and, in
the words of SQL Server Books Online (BOL),
“provide a significant
performance boost.” Web Listing 2 shows an
example of using SET
NOCOUNT on.
In a similar vein,
developers should also
avoid SELECT * queries
when returning result
sets, as these result
sets typically return
much more data than
the calling application
needs. Therefore, a great way to optimize code is to
make sure that you’re returning only columns that
you need; failing to do so results in additional network
overhead with no benefits. Likewise, another
good rule of thumb is to try to always horizontally
restrict returned data to only what’s needed
(through efficient use of the WHERE clause).
7. Improper Use of Cursors
Sadly, cursors are prone to abuse. But as long as
cursor-based operations aren’t causing massive performance
problems (or can’t be easily rewritten using
set-based logic), I typically encourage clients to use
optimized declaration syntax and ensure that cursors
are closed and de-allocated as soon as possible. A key
consideration that makes this recommendation possible
is the fact that most developers intend to use lightweight,
single-direction cursors for simple looping
operations. However, although this might be their
intention in most cases, developers will end up with
a much more expensive cursor unless they use very
explicit syntax.
The fix for this problem is usually simple: Use
more verbose syntax (as Listing 3 shows). By explicitly
declaring lightweight cursors designed for
use by the current connection only, and by ensuring
that cursors are closed and de-allocated (especially
when nested) as soon as possible, you can
overcome many of the problems typically associated
with cursors without spending a lot of development
effort.
8. Failure to Use Full-Text Indexing
In cases where SARGable queries aren’t possible
due to a requirement to search for %conditions%
in the middle text columns, full-text indexing can
be a lifesaver. Without full-text indexing, the best
you can hope for is an index scan (which requires
less I/O than a full table scan). But against millions
of rows, even an index scan can gobble up excessive
amounts of CPU resource, which can adversely
impact systemwide performance.
By tokenizing text data with a full-text index,
not only can you drastically decrease the size of
the indexes used for queries, but SQL Server can
provide results much closer to those you’d anticipate
with an index seek. The result is that, in
certain scenarios, full-text indexing can yield queries
that require substantially less processing and
return results at a fraction of the cost imposed by
their traditional counterparts. For example, the
data in Figure 2, page 16, was taken from an environment
where more than 8 million rows were being
pummeled up to 12 times a second by a fairly
complex query.
Best of all, these improvements can also free
up existing I/O and CPU resources—which translates
to better overall system performance. Therefore,
although full-text indexing can’t solve every
problem, you should evaluate it as a possible way
to boost performance if you’re doing intensive text
searches. There’s also a slight performance hit involved
with turning full-text indexing on, so make
sure you’re getting tangible benefits by going this
route.
9. Failure to Use Multiple Data Files
A key performance enhancement that many organizations
fail to take advantage of is SQL Server’s
support for multiple data files. In scenarios in
which a database is larger than the amount of
physical memory available, consider using multiple
data files, especially if the server in question has
multiple processors and one or more RAID controllers.
Furthermore, the benefits of using multiple
data files are even more pronounced in cases
where most of the database’s data is housed in a
handful of heavily used tables.
Finding the right number of data
files to use can require some testing,
but as long as you don’t go crazy
adding secondary data (.ndf)
files, it’s hard to go wrong.
In fact, Microsoft recommends
a ratio of roughly
0.25 to 1 data file per
file group per processor.
Why? Because adding
more files lets SQL
Server take advantage
of parallel processing
by delegating expensive
read operations to dedicated
I/O threads. The more
threads—up to a point—the
better. Therefore, moving a
heavily used table to a dedicated
file group, and its nonclustered indexes
to another dedicated file group, can
provide serious performance benefits—especially
if you can place the files for these file groups on
different physical disk arrays or spindles.
10. Failure to Properly Size Data Types
Failure to properly size data types can result in costly
performance problems that can be difficult to diagnose
and correct. This problem partly stems from
the fact that many developers don’t realize that as far
as SQL Server is concerned, a VARCHAR(20) is a
different data type than a VARCHAR(30). Throwing
NVARCHAR versus VARCHAR and NULL
versus NOT NULL into the mix only complicates
things further. Masking the problem, however, is the
fact that SQL Server typically handles subtle differences
by transparently converting one data type to
another in most cases.
Where this can cause problems, however, is when
coercion causes SQL Server to choose an index or
table scan instead of a much better-performing index
seek. On smaller tables, these effects are typically
negligible; however, as tables begin to grow,
the effect can become much more pronounced.
In some cases, this means that coercion can be a
“silent thief” that slows moderately sized queries
to a degree that usually goes unnoticed. However,
in extreme cases, I’ve seen examples where a
stored procedure declared an NVARCHAR(50)
parameter and used it in a WHERE clause against
a VARCHAR(50) column, which resulted in an
index scan that was making the query take four
seconds to execute. By simply changing the parameter
to match the table’s data type, SQL Server was
able to revert to an index seek that completed in
milliseconds. Therefore, although the frequency
of performance issues caused by this problem is
typically very low, it’s always a good idea to pay
close attention to data-type declarations and sizing
when coding to avoid having your code become
a sad statistic in the war on optimizing database
performance.
Performance Considerations
Optimizing SQL Server performance is a vast topic
that you can approach from many different angles.
This article focuses on a smattering of common
problems that are typically fairly easy to remedy. My
hunch is that most SQL Server deployments suffer
from a handful of issues outlined in this article—but
it’s my hope that this article has also gotten you to
think a bit more about ways to tackle performance
issues while you code, rather than after the fact.
End of Article
Prev. page
1
[2]
next page -->
