Partager via

Index maintenance script

  • Article

 

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

https://blogs.msdn.com/b/mosharaf/

https://www.msharaf.com/blog

Comments

  • Anonymous
    June 16, 2014
    Why not use the award winning scripts for Ola Hallengren http://ola.hallengren.com/ ?

  • Anonymous
    June 16, 2014
    I'm not a DBA in my role. Although Ola's script is award winning as you mentioned but I cannot recommend it to my customer as we should not have any preference. If my customer decided to use Ola's, great but if a customer asked me, I usually give my own script.