By: Jeremy Kadlec | Comments | Related: > Indexing
Problem
In SQL Server 2000, the max degree of parallelism or MAXDOP option is a server wide configuration indicating the total number of CPUs that the optimizer can use in parallel to complete a process i.e. index creation. When creating an index, the MAXDOP configuration was based on the server configuration as well as the current workload of the server. Typically, the MAXDOP was equal to the total number of logical CPUs, because this was the default on a multiple processor machine whether the logical CPUs were derived from multiple physical CPUs, dual core CPUs or multi-threaded CPUs. Unfortunately, no MAXDOP options were available when rebuilding indexes or when using the DBCC DBREINDEX or DBCC INDEXDEFRAG commands.
In SQL Server 2005 the rules have changed once again. With the enterprise edition of SQL Server 2005, you are no longer constrained by the default settings at the server level when creating indexes, but rather the MAXDOP value can be configured each time an index is created overriding the server configuration. In addition, the MAXDOP can be configured as indexes are rebuilt during maintenance windows or on an as needed basis which offers DBAs a great deal of flexibility to throttle the processing on machines with many CPUs (8, 16, 32, etc) and a large amount of memory (8 GB or more).
Solution
The MAXDOP configuration is now an optional parameter for the CREATE INDEX command with the SQL Server 2005 enterprise edition. The MAXDOP value limits the total number of logical CPUs used in parallel to create the index with a upper limit of 64 to perform the index scanning and sorting processes.
- A MAXDOP value of 1 serializes the processing.
- A MAXDOP value greater than 1 restricts the maximum number of processors used in a parallel to the number specified or less based on the current processing.
- A MAXDOP value of 0 uses all of the logical processors on the server or less based on the current processing.
The ALTER INDEX REBUILD that replaces the DBCC DBREINDEX command also supports a MAXDOP configuration with a similar set of functionality as the CREATE INDEX example above.
Next Steps
- The MAXDOP configuration offers a new set of possibilities with the SQL Server 2005 enterprise edition to enhance the index creation and index rebuilding process on servers with large amount of resources i.e. CPU and memory.
- Start researching and testing the MAXDOP configurations in your environment as you begin to migrate index code from SQL Server 2000 to 2005.
- For information on database fragmentation requiring index rebuilds, check out the SQL Server 2000 to 2005 Crosswalk - Database Fragmentation tip.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
