MAXDOP limits supported parallel index operations without changing unrelated instance settings. I choose the ceiling from the maintenance workload.
During performance health checks, we discuss how index maintenance competes with the application. More parallel workers can finish a suitable operation sooner, but they can also increase resource pressure. The goal is an acceptable maintenance window and workload impact.
Set the limit for one operation
-- Administrator examples, not a tuning recommendation for every table.
CREATE INDEX IndexName ON dbo.TableName (Column1, Column2)
WITH (MAXDOP = 8);
ALTER INDEX IndexName ON dbo.TableName
REBUILD WITH (MAXDOP = 8);These are administrator templates. Replace object names only after deciding that maintenance is needed. MAXDOP 8 sets a ceiling for the operation. It does not guarantee eight processors or a faster result.
Check support and scope
Parallel index operations are edition-dependent. Check the feature table for the exact SQL Server version and edition. An ordinary parallel SELECT plan does not establish support. Index MAXDOP applies to supported builds and rebuilds, rather than REORGANIZE.
The operation-specific limit does not change the instance setting and is not a persistent promise for future maintenance. Workload, available resources, and the operation influence the actual degree of parallelism.
Measure the tradeoff
Compare duration, CPU, memory, I/O, and application response under representative conditions. Include the locking and edition limits of online operations where relevant. No single degree is right for every table or maintenance window.
Related reading
An index MAXDOP value is not a speed guarantee, it is a limit on supported parallel work.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
