And some more about parallelism…

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?