“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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

NOLOCK hint in SQL Server, Good guy or bad guy?

Introduction

It is quite often in production environment that multiple application and/or users try to access same resource at the same time which may create locking and blocking issues. It is tricky to deal with this situation as there is no rocket science which suits for all situations. It is all depends on situation because in some production environment, it is not possible to resolve blocking issues easily over the night.
The main root cause for locking is, we have long running transaction which keeps your object locked and meanwhile any request comes to access the same object, has to wait until the current transaction complete its operation. Best choice should be to minimize the transaction length so that it releases the lock quickly and other request doesn’t need wait due to lock but unfortunately it is possible to solve it easily in ALL environments.

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.
Connect to your SQL Server and open three query window. We will call these query windows with following name:
·         Win1
·         Win2
·         Win3

How to do it…

1.       After connecting to SQL Server Management Studio (SSMS), open Win1
2.       Create one sample database, named SQLHub and create one table with sample rows with following T-SQL script:
create database SQLHub
GO
USE SQLHub
GO
–if orders table is already there. you can delete it than create new one with name “Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLE orders
END
GO
–creating table
CREATE TABLE orders(OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting 100000 sample rows into table
INSERT INTO orders(OrderDate, Amount, Refno)
SELECT TOP 100000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
3.  Execute one UPDATE statement in Win1 with BEGIN TRANSACTION. Note that we don’t have COMMIT or ROLLBACK after the UPDATE Statement at the moment. ROLLBACK is commented so it won’t be executed.
BEGIN TRANSACTION
UPDATE Orders SET Amount=5.00 WHERE OrderID<10
–ROLLBACK
4.  In Win2, try to execute following SELECT statement:
select * from Orders Where orderID<=15
5.       You will observe that SELECT statement wouldn’t return any results so in WIN3, try to execute following T-SQL and know what is going on behind the screen:
sp_who2
go
6.       We will get many rows in the result set from the above command but we have to look at last two rows of SQLHub database as per given in following screen capture:
7.       We can see that out UPDATE statement runs with SPID 54 and SELECT statement runs with SPID 55. SELECT query is blocked by SPID 54 given in row no.2 column no.5 in screen capture. Now I have two ways. Either I issue COMMIT / ROLLBACK command which is not a good idea to interrupt UPDATE or cancel SELECT query in SSMS which I will do now.
8.       Since we have cancelled SELECT query, we will not execute same SELECT statement with NOLOCK hint.
select * from Orders WITH (NOLOCK) Where orderID<=15
9.       Here is the result return by above query.
10.   Go to Win 1 and execute “ROLLBACK” statement.
11.   Go back to Win 2 and execute SELECT statement with or without “NOLOCK”. For eg:
select * from Orders  Where orderID<=15
12.   Here is the screen shot of result return by above query:

How it works…

When we have executed UPDATE statement in Step no 3 without COMMIT or ROLLBACK, It updates the records but didn’t release the lock it has acquired on the table so SELECT query was not able read data and return it.
How do you know that whether UPDATE has really updated the values or not? Since our select query is not giving results. We have executed same select query with “WITH (NOLOCK)” hint in step no. 8 and we can confirm that values are update with the screen capture given in step 9. Isn’t it good situation? Yes, may be as we were not even able to get the result of SELECT statement in step no 4. But I would say we can’t decide whether it is a good or bad without evaluating the business need.
Since UPDATE statement has updated the value but it is not saved so there is a chance to ROLLBACK. If you see resultset given in steps no 9, you will see “5.00” in “Amount” column which may not be a proper value as after ROLLBACK, it comes back to “2.00” again as per step no. 12.
Think if this would be a result of stock exchange, what would happen? Can we run with dirty read we seen in screen shot in step no.9? No certainly not that is why I have conveyed that NOLOCK is all depends on the business need and situation. Use it wisely as it is two sided sword.

See also

NOLOCK as known as READUNCOMMITED concept is somehow related to ISOLATION level.  Here are the links of some of my past article on the subject.
Happy Reading!!!
Reference: Ritesh Shah
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah