SQL SERVER INDEX OPERATIONS ON STEROIDS

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: the load on SQL Server, out of date statistics, the server configuration setting for degree of parallelism, amount of available memory/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 or the load. This means it could either grant or limit the number of CPU’s Index operations can use. In many cases this is the best practice and you should not change the default value.

Let’s welcome MAXDOP

If you are using the Enterprise Edition of SQL Server 2008 (or even SQL2K5), you can now control the number of processors/CPUs that can be used for performing index operations by using MAXDOP option. This 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: Creating an index, Altering or Rebuilding an index, Dropping a Clustered Index, etc.

Once you apply the MAXDOP option for index operations, it will override the max degree of parallelism server configuration option for index operations. (All other Index operations with no MAXDOP will continue to honor the max degree of parallelism settings)

MAXDOP supports three values

MAXDOP=0 (This is the default setting for SQL Server. This instructs SQL Server to grant CPUs based on the available resources and server load.

MAXDOP=1 (This disables parallelism and enables the operations to execute serially)

MAXDOP=2 through 64 (This indicates SQL Server to override the max degree of parallelism setting and use the number of CPUs specified here)

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

Let's take an example of how we 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)

The above statement will override the current SQL Configuration "Max Degree of Parallelism" because we are now using MAXDOP option.

Thank you,