Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Max Degree of Parallelism for Index Creation and Rebuilding


By:   |   Last Updated: 2006-07-27   |   Comments   |   Related Tips: More > 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.


Last Updated: 2006-07-27


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools