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.