Reindex Procedure with a Twist (or two)

We’ve all seen reindex procedures that rebuild all indexes in a database or all indexes in a database which have less than a specified ScanDensity. This one goes a few steps further. First, the DBA can specify a ScanDensity_Threshold (a min value for ScanDensity), plus a Limit (qty or %). If you specify a ScanDensity_Threshold of 80, and a Limit of 50%, then only the worst 50% of all indexes in the database with a ScanDensity < 80 will be rebuilt. It determines the “worst” n% of all indexes by calculating a Reindex_Factor based on ScanDensity and PageCount. An index with a ScanDensity of 70 and 2000 pages will be reindexed before an index with a ScanDensity of 25 and 2 pages. After it finishes, you can query the DBA_Index_History table to see which indexes were rebuilt and why. The DBA has the option to specify a FillFactor_Age_Limit. If an index is rebuilt a second time within the FillFactor_Age_Limit timeframe, the FillFactor will be reduced by 5, to a minimum of 70. (If this is not desired, simply set FillFactor_Age_Limit = 0). An alert can be sent to the DBA when an index’s FillFactor reaches 70 (commented out. You may insert your own alert method). This procedure is written to take advantage of a “DBA” database which I have on each of my SQL Servers. If you store your maintenance procedures in the master database, you will need to take this into account, but I highly encourage you to move them into a “DBA” database. It greatly simplifies management! There is much documentation in the code. Please examine the procedure carefully understand how it works before you implement it. This version of the procedure is only tested in SQL 2000. With that, I hope you find this procedure as useful as I have.