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,
4. Amount of available memory
5. Amount of available resources,
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.
With the release of SQL2K5 onwards, SQL Server has introduced a new indexing option called MAXDOP. With MAXDOP, you can now control the number of processors/CPUs that can be used for performing index operations. MAXDOP 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.