“Max degree of parallelism” or “MAXDOP”, good guy or bad guy?

Introduction

Yesterday I have written article for NOLOCK in good guy or bad guy series. Today I am presenting one topic on the same subject which is “max degree of parallelism”. This is also one of the topics which can raise the big debate on. Now a days people used to have powerful servers with multiple processor for processing their database request and they tend to believe that more processor can perform well always to process the query which is a myth but in reality more processor can performs well for some query but perform really worst for others too so rather then setting up a fix figure to process each query, I believe to leave the decision on SQL Server as SQL Server 2012 has such a smart engine which can decide whether to go for parallelism or not.

As long as my personal practise concern, I used to use this option at very last resort as most of the query performance can be done via following:

• Proper Index
• Properly written query which can use index
• Updated statistics

Getting ready

I am going to perform this example in my SQL Server 2012 RTM version but it may work as it is in SQL Server 2005 / 2008 too.

Deciding the processor for the query could be done via following two popular ways:

·         Set server wide value with option “max degree of parallelism” via “sp_configure” stored procedure

·         Use the MAXDOP hint

In this article I will show both ways in coming sections.

We are going to use “orders” table in this article, you can get the script to generate that table from previous article, click here.

How to do it...

1.       After connecting to SQL Server Management Studio (SSMS), open new query window.

2.       Set the 0 for “max degree of parallelism” in “sp_configure” system stored procedure with following T-SQL:

--enable advanced option in sp_configure procedure

EXEC sp_configure 'show advanced option',1

RECONFIGURE WITH OVERRIDE

GO

--setting 0 for max degree of parallelism

--0 is the default value so even by executing

--following command, we are not making any change

sp_configure 'max degree of parallelism', 0

RECONFIGURE WITH OVERRIDE

GO

3.  Before we test any query let us clear cache and buffer so nothing will be used from saved execution plan. I highly recommend using following command in development or testing server only. Clearing cache and buffer in production server may give you slow performance temporarily.

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

4.  Now specifying parallelism at query level with MAXDOP hint by executing following T-SQL

set statistics IO on

set statistics time on

select * from orders option (MAXDOP 1)

set statistics time off

set statistics IO off

set statistics IO on

set statistics time on

select * from orders option (MAXDOP 2)

set statistics time off

set statistics IO off

5.       Since we had “Statistics IO” and “Statistics Time” enabled while executing both of the above SELECT query. We can get results of SET commands in “Message” tab besides “Results” panel. Here is the result I am getting in my testing server, you might get different result.

(100000 row(s) affected)

Table 'orders'. Scan count 1, logical reads 409, physical reads 0, read-ahead reads 414, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 16 ms,  elapsed time = 586 ms.

(100000 row(s) affected)

Table 'orders'. Scan count 1, logical reads 409, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 62 ms,  elapsed time = 575 ms.

How it works...

Setting value 0 (zero) either in MAXDOP or for “max degree of parallelism” indicate that SQL Server has choice to decide whether to use multiple processor for the query or not. Apart from 0, any number specified more then 0 indicate that, maximum upto that number of processor could be used by processor.

We have used MAXDOP 1 for first SELECT query and MAXDOP 2 for second SELECT query and you can see results of “STATISTICS TIME” that one processor has done the better job by consuming 16ms in CPU time as against 62ms CPU time in second try where we had MAXDOP 2 which is indeed a big difference.

CPU time shows the time it has taken to process the query.

Conclusion

If there is no other alternative, then and then go for selecting the processor for your query otherwise let SQL Server take decision by its own.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com