I recently had the need to look up all fragmented indexes on a specific database, to determine if they were the reason for less than optimal performance. I wrote the following script, which allows you to pick a specific database to check for fragmented indexes, and decide to just view them or reorganize/rebuild them, and also the fragmentation threshold percentage at which to run these operations. Then I thought it might be useful to others too, so here it is.
Usage involves setting the following five parameters (technically, variables) at the top of the script:
@DATABASE_NAME: set to whatever database you want to check for fragmented indexes. This must be on the same server that you are running the script on.
@REBUILD_THRESHOLD_PERCENT: the fragmentation percent above which you want to rebuild indexes.
@REORGANIZE_THRESHOLD_PERCENT: the fragmentation percent above which you want to reorganize indexes.
@EXECUTE_REBUILD: set to 0 to only view fragmented indexes. Set to 1 to actually execute the rebuild. Note that this goes hand in hand with the variable @REBUILD_THRESHOLD_PERCENT
@EXECUTE_REORGANIZE: set to 0 to only view fragmented indexes. Set to 1 to actually execute the reorganization. Just like the rebuild threshold percent variable above, this acts in sync with the variable @REORGANIZE_THRESHOLD_PERCENT
The included screenshot shows a sample run of this script against the AdventureWorks2012 database, with the top 10 records returned by the script. If the execute variables were set to 1, these results would not be immediately repeatable because the fragmented indexes would get rebuilt / reorganized after these results were displayed.
