Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter