Another script that I wanted to share for so much time but didn't have the commitment to be persisted with my postsJ. Using the built-in SQL Server maintenance plans for index maintenance works well for small databases without HA solutions that are affected by the amount of transaction logs generated. When it comes to mid-size or large databases, the process of reorganizing or rebuilding the indexes has to be optimized. In this case, there has to be a script used to reorganize/rebuild only the indexes needed to be maintained. There are many factors that influence the decision like
The script is taken care of all these. At the beginning of the script, there's a part where I set the default values for these configurable variables.
SET @useOnlineIndexing = 1;
SET @SORT_IN_TEMPDB = 'OFF';
--change it to ON to sort in tempdb
SET @minPagestoConsider = 1000;
SET @MDOP = 8;
--Max Degree of Parallelism. Change it to 1 to reduce the number of CPUs involved in rebuilding the index
SET @MinNumberOfScansToConsider = 1000;
--how many range scans to consider
SET @WhenPageLockOFFandReBuilding = 'EnablePageLock';
--either 'EnablePageLock' or 'SetMDOP1'
/* When building index online with MDOP>1 & Page locks disabled, would cause more fragmentation. deciding the best action either enable Page locks then disable it
or set the MDOP=1 */
One important option is when rebuilding index online while MDOP>1 and Enable Page Lock disabled, you will notice that fragmentation is increased not decreased. That's a known behavior documented on this KB article. The script give you two options, either to Enable Page locking before the rebuilding and disable it after rebuilding or make MDOP=1.
The script works against one database. If you would like to run it against more than one database, use the SQLCMD variable named $(DatabaseName) to target multiple databases using SQLCMD calls in a batch file or using a SQL Job.
The script can be downloaded from here
This blog post is cross posted on