Using MAXDOP to Speed Up Index Creation

Ever noticed that even though you have a beefy server, SQL Server index operations sometime takes time? There are many things that happen behind the scenes that could contribute to the slowness of index operations.

Some of the reasons include:

1. Load on SQL Server
2. Out of date statistics
3. Server configuration setting for degree of parallelism (MAXDOP)
4. Amount of available memory
5. Amount of available resources, etc.

SQL Server can intelligently detect the load on the server and auto-adjusts the amount of resources that can be allocated to a process. In the case of Index operations; if SQL Server is busy, it will automatically adjust the Max Degree of Parallelism server configuration setting to accommodate the process load. This means it could either grant or limit the number of CPUs Index operations can use. In most of the cases this is the best practice and you should not change the default value.

However, SQL Server can also use the index hint called MAXDOP. With MAXDOP indexing hint, you can now control the number of processors / CPU’s that can be used for performing index operations. Using MAXDOP indexing option enables parallelism for Index operations, which means it can use multiple processors to fulfill a single query statement which can potentially improve the performance of index operations such as:

1. Creating an index
2. Altering an index
3. Rebuilding an index
4. Dropping a clustered index, etc.

When you use the MAXDOP hint for any index operations, it will override the server level Max Degree of Parallelism value for that specific query. (All other Index operations with no MAXDOP will continue to honor the server level max degree of parallelism settings)

MAXDOP hint supports three values when used with any indexing operations:

1. MAXDOP = 0 This is the default server level setting. This instructs SQL Server to grant CPU’s based on the available resources and server load.
2. MAXDOP = 1 This value disables use of parallelism and enables the operations to execute serially.
3. MAXDOP = 2 through 64 This value indicates SQL Server to use the number of CPU’s specified in the value to process index operations.

Keep in mind that if your SQL Server has only 8 CPU’s and you specify more CPU’s (Ex: MAXDOP = 32) then SQL Server will default to the number of CPU’s available on the system.

Here’s an example of how you can write an Index creation statement that would use 6 CPUs out of 8 when creating an index:

Create NonClustered Index EmailAddress_IDX on
  Employee.Contact(EmailAddress)
With (MAXDOP=6);

Note: Please make sure to test this option in the test environment before implementing it in the production environment.