Maximum degree of parallelism

• thinknight

  SSCrazy

  Points: 2068

  More actions

  November 13, 2008 at 8:22 am

  #124306

  Hi,

  How do i see maximum degree of parallelism in both sql server 2000 and sql server 2005?

  And can i configure in way?

  Thanks

• Elisabeth Rédei

  Hall of Fame

  Points: 3873

  More actions

  November 13, 2008 at 1:52 pm

  #898302

  Hi,

  On both 2000 and 2005 you can run the following statement to set the server-side setting:

  sp_configure 'show advanced',1

  reconfigure with override

  go

  sp_configure 'max degree'

  go

  sp_configure 'show advanced',0

  reconfigure with override

  To set it, for example to 4, you would issue (on both versions)

  sp_configure 'show advanced',1

  reconfigure with override

  go

  sp_configure 'max degree', 4

  go

  sp_configure 'show advanced',0

  reconfigure with override

  The recommendation is to set it to a number equal to the number of physical processors. In general, parallelism doesn't work very well with OLTP workload but having said that, most systems tend to have somewhat of a mix of OLTP and more reporting like activities.

  Ref: "General guidelines to use to configure the MAXDOP option", http://support.microsoft.com/kb/329204.

  You can also adjust the degree of parallelism for individual queries with the MAXDOP hint (OPTION (MAXDOP n)).

  HTH!

  /Elisabeth

  elisabeth@sqlserverland.com
  MCITP | MCT
  http://sqlblog.com/blogs/elisabeth_redei/
  http://linkedin.com/in/elisabethredei