| |
 |
|
SQL Server Tips by Robin Schumacher
|
Index Maintenance Plans
To keep indexes in top structural shape, maintenance will be
required from time to time. This equates to keeping statistics
updated along with periodically reorganizing indexes.
SQL Server is designed to update index statistics automatically
through the setting of the AUTO UPDATE STATISTICS option, but many
DBAs are not so trusting. The DBA can build a SQL Agent job that
executes the sp_updatestats procedure. This updates all the
statistics in the target database on a schedule that matches the
dynamics of the underlying database.
The DBA can also make use of Enterprise Manager/SQL Management
Studio’s maintenance plan wizard to create a job that updates the
statistics of databases. There is more to the maintenance plan
wizard than just statistical update functions, so one should take
the time and look through what Microsoft offers.
The last time indexes were statistically updated can be checked
through use of the stats_date function. For example, to see a
listing of indexes and the date of their last statistics update, the
last_index_stats query can be executed:
* last_index_stats
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
index_name = object_name(id) + '.' + name,
stat_update_date = stats_date (id,indid),
indid,
rows
from
sysindexes
where
indid > 1 and
indid < 255 and
name not like '_WA_Sys%'
order by 1
One should not be too concerned if a NULL output for the statistics
date is seen as the underlying table may have never had any rows in
it. These objects can easily be filtered out in the above query by
adding a predicate of and rows > 0.
Chapter 5 of this book covered how to determine what indexes need to
be reorganized, so once again, the chapter should be reviewed for
reference material and procedures that provide the diagnostics
needed. However, if there are many servers to deal with, it is
unlikely time is available to periodically review each database on
every server and build reorganization plans on the fly.
The best thing to do is set up smart reorganization jobs that
periodically interrogate the active databases and dynamically
reorganize only those objects that exceed the predefined thresholds.
For example, in SQL Server 2005, this is relatively easy to do;
simply execute the up_index_reorg_2005 procedure below for the
databases of interest.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
