Maintenance
When data changes, we need to perform
maintenance. Maintenance helps keep your
tables contiguous and your pages denser; it
also helps improve resource utilization and
therefore improves overall performance. But
how do you maintain your tables, and what
does it really mean to defragment a table?
This topic is fairly complex, but here are a
few things to think about and review.
In SQL Server 2005, you can use the
sys.dm_db_index_physical_stats dynamic
management function to determine how
fragmented your table is. Use this function
to get a feel for how much and how fast
fragmentation is occurring. Also, consider
rebuilding your table if you have 25 percent
or greater fragmentation, you have a dedicated maintenance window and ample disk
and log space, and you can rebuild online.
Consider defragmenting your table if you
can't rebuild online, you don't have more
than 25 percent fragmentation, and you can't
afford the disk or log space to rebuild.
Statistics
Because statistics are the optimizer's source
for estimating how much data will be
processed, they're crucial for better performance. Statistics are used with indexes,
but standalone statistics (statistics that are
created in addition to indexes) can also
help to improve a variety of optimization
strategies as well as improve the usefulness
of a composite nonclustered index. This is
also complex, but here are a few things to
think about and do: First, make sure that the
database option "auto create statistics" is on.
(It's on by default.) Second, make sure that
the database option "auto update statistics"
is on. (It's on by default.) Third, consider
using the sp_createstats stored procedure to
create additional statistics on the secondary columns of your nonclustered indexes. Use
the following command:
sp_createstats ‘indexonly',
‘fullscan'
Finally, realize that certain correlations
between columns can't be perfectly described
by statistics alone. Sometimes the only way
to answer a query is with the right index.
Results Match Effort
If you design with performance in mind, the
rewards to your business can be huge. With
careful thought and attention to indexing,
maintenance, and statistics, as well as database
design, you'll be the hero who designed for
performance—and achieved it.