SQL Server parallelism on server level and MAXDOP

Recently, at a SQL Server conference, the parallelism topic was brought up. One of the participants in the conversation expressed opinion that once the parallelism for the SQL Server instance is disabled, there is no way a query will be executed in parallel.

At the time I just expressed my doubt and my hope that this statement was not true.

So, just now I found some time to test this case. Here is how it goes:

Let’s say you have a SQL Server 2005 installed on a dual core machine. The default setting of the installation is Max degree of parallelism set to 0. (i.e. uses all possible cpu cores).

Let’ execute the following query:

USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style
GO

So, the query optimizer chooses parallel execution.

Now, let’s set the server level degree of parallelism to 1 by using the following query:

EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

And after the parallelism is set to 1 on a server level, let’s execute the same query as before, but this time let’s use the OPTION(MAXDOP x) setting.

USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style
OPTION (MAXDOP 2)
GO

So, if we look at the query plan, we notice that the query is still executed in parallel.

What is the bottom line?

The server level setting CAN be overwritten on a query level. It only makes sense, when we think about it: if one can override the server level setting when it is set to 0, then why wouldn’t we be able to override it when it is set to 1?

Note: there are 3 levels of managing parallelism:

  • on a server level
  • on a query level
  • on an index level

 

 

2 comments to SQL Server parallelism on server level and MAXDOP

  • Hi,
    Do you mind to describe further what is the different if we set the server level degree of parallelism to 1? Does it mean SQL Server will only run using 1 CPU processor, if the server has 2 processor?

    Do you mind to explain further on “What is the bottom line?”. I do not really understand why it being overwritten. Thanks and appreciated.

    • Feodor

      Jimmy,
      parallelism in SQL Server is a ‘make it or break it’ asset, and the full control of it can only bring endless benefits to the database system.

      I am preparing a blog post on parallelism soon, but for now let me describe it with a few words: parallelism is the ability of queries to take advantage of more than one CPU unit during execution. (by ‘CPU unit’ I mean CPU core, since SQL Server is core aware and not CPU aware)

      Having said that, let me go in a bit of detail on levels of parallelism.

      As I stated in the article above, the parallelism can be controlled on a several levels:

      on a server level
      on a query level
      on an index level

      The server level is the most ‘generic’ one, i.e. can be overwritten by the rest.
      Then you can specify MAXDOP option in the query definition, and it will overwrite the server setting during execution.
      Also, you can specify the parallelism level for each index, and this will also be effective during the query execution, while using the index, regardless of the MAXDOP option of the query itself.

      Hm, this comment got a bit ahead of my article which is coming soon. :)

      Feodor