Automatically Rebuild or Reorg index based on fragmentation
The Attached SQL Query can be safely used for rebuilding or reorganizing all indexes in a particular database based on the fragmentation level.
Note: The script is currently set to perform offline index rebuild since online index rebuild is supported only in Enterprise edition SQL Server. Inorder to change it into online mode, add , ONLINE = ON statement along with
FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON
in line 100 in the attached script.
3203_AutoIndexRebuild2005andAbove (Click to download the script)
Usage
- Create a new job and insert the script as a step.
- Modify line numbers 39 and 40 and provide values wisely. If you feel like your indexes are heavily fragmented and most of the indexes are above 80{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da} fragmented, It will be better to use higher value for @minfrag variable inorder to prevent this script from running too long.
- Update line 100 refering above note to make rebuild operation online for Enterprise Edition SQL Server.
- Trigger the job manually or schedule the job to run when there is least activity on the database.
Status update
This will be helpful when you feel like the script is running for long time and you need to see where the script is currently working. I have used two global temporary tables for the same. Table ##fraglist for seeing the fragmentation report for all the indexes and ##fragstatus for us to see where the script is currently working. The ##fragstatus table will have the list of index names which got rebuilt or reorganized. From that we can predict ETA for the whole process.
I will be releasing a newer version for this script soon in which we will get the information on the activity performed for each index. ie, if rebuild or reorg happened. Also I will keep an option to pause and start the activity from where it stopped last time.
Hope this helped. Thank you for reading.
