Index maintenance script
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
- Number of pages in the index: Small indexes will not cause problem because SQL will read them one time and they stay in memory
- Fragmentation percentage: small fragmentation makes more sense to have them reorganized. Big fragmentation will be rebuilt much faster than reorganization
- Whether or not will use online index rebuilding
- Whether or not sorting in the tempdb (recommended if the tempdb on a faster disk)
- Number of range scans on the index. If we have an index with mostly singleton searches then there's no need to defrag it.
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 values
--------Configuration-----------
SET @useOnlineIndexing = 1;
SET @SORT_IN_TEMPDB = 'OFF';
--change it to ON to sort in tempdb
SET @minPagestoConsider = 1000;
--8MB indexes
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