Note: The formulas presented here assume sp_configure max degree of parallelism=0 and no other resource governor or query option was established. You may use the MAXDOP query or resource governor options to alter the runtime DOP selections.
In my test case the I had 64 CPUs so the MIN becomes the partitions = 27. This is a very practical choice in many situations as the partitions usually line up with hardware and running DOP = partitions in this way is very efficient and inline with the DBAs database design decisions.
The specific index build I was doing was very CPU intensive (Spatial) activities and from testing I knew if I achieved additional runtime DOP I could build the index faster (knowing I consume more resources at the possible expense of other activity!)
Evenly splitting the partitioned table into at least as many partitions as I have MIN(64, CPUS) CPU resources allowed me to apply more CPU resources to the create index operation.
In my specific scenario the 1.3 trillion row, spatial index builds in ~4.5 hours @ 27 CPUs and ~2.3 hours @ 64 CPUs.
WARNING: Increasing the runtime DOP does not always provide improved performance. The additional overhead may put pressure on memory, I/O and impact performance of other queries as the additional resources are consumed. You should test carefully but consider your partitioned layout in order to optimize your DOP capabilities.
Specific Partition Index Builds
You should also be aware that the partition scheme and index may limit the MAXDOP when rebuilding a specific index on a specific partition. For some indexes you are allowed to rebuild a partitioned index for a specific partition. This may use the partition’s range and limit the index rebuild to MAXDOP=1 where a similar index build on a non-partitioned table may use an increased DOP level.
Bob Dorr - Principal SQL Server Escalation Engineer
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.