I have been doing some more looking at SQL Server indexes lately and came across a great script that may be of use to others as well.
So what can this index defrag script do?
- Schedule it to run with the default settings; it works “right out of the box” with no additional configuration necessary
- Run this one script from a centralized database for all databases on a server
- Run this script for a specific database or table
- Configure custom threshold limits and the point at which a rebuild should be performed (instead of a reorganize)
- Defrag individual partitions
- Log its actions and the duration of the defrag
- Run in “commands only” mode (@executeSQL = 0, @printCommands = 1)
- Customize performance parameters such as @maxDopRestriction and @defragDelay to minimize impact on the server
- Schedule specific indexes to only be defragged on weekends, or every other day
The script source code (very well documented) and author (Michelle Ufford) can be found at http://sqlfool.com/2009/06/index-defrag-script-v30/