As you are aware, MAXDOP value is set to 0 when you do fresh installation of SQL Server on your server. Let’s understand why we should not leave it at its default value which is 0.
Maxdop(Maximum Degree of Parallelism) 0 is as good as using all your processors on that server(unless you have more than 64 cores). I shall be using stackoverflow database here for demonstration purpose. You may download the same from here.
MAXDOP 0
For understanding purpose, I am using a simple query and then grouping by same set of columns with an additional aggregate function i.e. count. My query is as below:
SELECT [AboutMe]
,[Age]
,[CreationDate]
,[DisplayName]
,[DownVotes]
,[EmailHash]
,[LastAccessDate]
,[Location]
,[Reputation]
,[UpVotes]
,[Views]
,[WebsiteUrl]
,[AccountId], count() FROM [StackOverflow].[dbo].[Users] group by [AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,[Location] ,[Reputation] ,[UpVotes] ,[Views] ,[WebsiteUrl] ,[AccountId] having count() > 1 option (maxdop 0)
Once I start executing above query, I went ahead and checked CPU utilization and as expected, it is using all my processors:
MAXDOP 8
Next was to change maxdop value to 8 since I have 8 cores at my PC and query which I used is as below:
SELECT [AboutMe]
,[Age]
,[CreationDate]
,[DisplayName]
,[DownVotes]
,[EmailHash]
,[LastAccessDate]
,[Location]
,[Reputation]
,[UpVotes]
,[Views]
,[WebsiteUrl]
,[AccountId], count() FROM [StackOverflow].[dbo].[Users] group by [AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,[Location] ,[Reputation] ,[UpVotes] ,[Views] ,[WebsiteUrl] ,[AccountId] having count() > 1 option (maxdop 8)
Post executing the query, within few seconds, CPU again started hitting the roof and utilization is 100%:
Needless to say, both the queries took exactly same time(19 seconds individually) and even their query plan is identical:
MAXDOP 4
As per Microsoft’s recommendation KB2806535, I would changed the value of MAXDOP to 4 (it could be any number below 8). Let’s see how the same query performs and how many core of processor does it actually use. We shall use below query:
/* MAXDOP 4 */
SELECT [AboutMe],[Age],[CreationDate],[DisplayName],[DownVotes],[EmailHash],[LastAccessDate],[Location],[Reputation],[UpVotes],[Views],[WebsiteUrl],[AccountId],
count() FROM [StackOverflow].[dbo].[Users] group by [AboutMe],[Age],[CreationDate],[DisplayName],[DownVotes],[EmailHash],[LastAccessDate],[Location],[Reputation],[UpVotes],[Views],[WebsiteUrl],[AccountId] having count() > 1 option (maxdop 4)
The maximum CPU utilization peaks around 60%(extra 10% lasted for only few milliseconds and it was for master thread), query completes in 26 seconds(taking 7 more seconds than previous execution). This time we have utilized only 4 cores and left 4 cores for other processes. When you scale this for a gigantic server, you could save lot many cores without compromising much on duration. This query plan looks similar to what we had earlier except the difference that number of cores utilized here are halved:
MAXDOP 6
I did another testing with MAXDOP 6 and figured out same query finished in 24 seconds. You would need to decide whether getting 2 cores spare is more valuable or saving 2 seconds. We have another very important factor to consider and that is “Cost Threshold for Parallelism”. This shouldn’t be left at its default value to 5 and starting value could be anything 50 or more however that needs to be analyzed before arriving at final value.
Changing MAXDOP
Maxdop value can be changed at session level as we have observed in above examples. This can be changed at instance level too by using below commands:
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure ‘max degree of parallelism’, 4;
GO
RECONFIGURE WITH OVERRIDE;
GO
This can also be changed at UI(User Interface) level by going into advance setting of Properties tab as below:
Improper setting of MAXDOP settings would result in CXPACKET wait and we shall discuss them in upcoming post.
Please leave your comment if you liked this post or have any feedback.