articles >> performance tuning >> Automating Reindexing In SQL Server 2000 ...

Automating Reindexing In SQL Server 2000

By : Tom Pullen May 04, 2005

Page 4 / 4


The above is a screenshot of the output text file from this SP opened in Excel. Some columns have been deleted for the sake of brevity. When opening it, you need to follow the open Text File wizard – chose Fixed width and open the Import at Row 3.

Here, you can review the scan densities of the tables in your database which have been checked.

Further down the output file (after the data output from DBCC SHOWCONTIG), you will find the text output which details which tables and which of their indexes are being reindexed, with the start and finish times of the reindexing section at the beginning and end of this part. An example is shown below.

 

Why Not Use DBCC INDEXDEFRAG() to Reduce Blocking?

The answer is, use it if you want or need to! If your requirements are 24X7 operations and the exclusive table locks that DBCC DBREINDEX() take are unacceptable to your business, you can substitute it for DBCC DBREINDEX(). However, you will need to adapt the syntax as they are not the same (thanks, Microsoft!). If you do not know the difference, here is a brief summary: DBCC DBREINDEX() is a complete, through, bottom-up index recreation operation which will exclusively lock the table while it runs by necessity. DBCC INDEXDEFRAG() is a less complete, but online operation, which will attempt to improve your indexes’ condition as far as it can while not causing blocking and (hopefully) not disrupting OLTP. I must admit I have never used DBCC indexdefrag(), as my systems do not have strict 24X7 no-blocking requirements, fortunately, so I cannot vouch for is efficacy or otherwise. I have read that it is not as effective as a full DBCC DBREINDEX. However, it will surely be better than nothing, so if your database runs a global website which must never stop, as is common nowadays, you should adapt this stored procedure to use it instead.

 

Add it as a Scheduled Task

This is a relatively trivial task for a moderately experienced DBA, so I will not include code for it here. Add it as a stand-alone task or include it as a step in your existing maintenance job. Make sure you set the job-step or job to output to a text file so you can retain and look at the all-important output file.

 

Conclusion and Summary

Hopefully, this code and article will help you to achieve a better and more refined approach to your database server maintenance. Maintenance windows nowadays are precious commodities and should be kept to an absolute minimum in terms of both impact on users and duration whilst also preserving an efficient and well-maintained database server. Implementing this procedure as a scheduled task, with careful monitoring of its output, should help you, to some degree; achieve your ends more effectively.


 

Tom Pullen has been a DBA for 6 years. He currently works for Oxfam, a large UK charity. He has experience in SQL Server 6.5, 7.0 and 2000. He is married with two children. Contact him at: tpullen@oxfam.org.uk

Copyright 2004 by the author.