THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: A Hint of Degrees

This is just a quick post to describe a test I just ran to satisfy my own curiosity.

I remember when Microsoft first introduced the query hint OPTION (MAXDOP N). We already had the configuration option ‘max degree of parallelism’, so there were lots of questions about how the hint interacted with the configuration option. Some people thought the configuration option set an absolute maximum, and the hint could only specify something less than that value to be meaningful. Other people thought differently, and I actually can’t remember what I thought at the time. All I  remember is that there was confusion. So I decided to test it recently.

I have a machine with 8 logical processors, so I can have a degree of parallelism up to 8. And my first test showed that all 8 were used when I left the configuration option set to the default and didn’t use a hint.  I ran this test on both SQL Server 2012 SP1 and SQL Server 2014 RTM. The results were the same. I needed to look at the actual execution plan, which runs the query, because the decision of how what degree of parallelism to use is not determined until runtime. If I look at an estimated execution plan, I will see a parallelism operator, but not degree of parallelism will be reported, because it is not known.  I can look get the actual plan either by using the button on the Management Studio toolbar called “Include Actual Execution Plan”, or I can use SET STATISTICS XML ON.

So, as mentioned, the first test showed a degree of parallelism of 8:

USE AdventureWorks2012;
GO
SET STATISTICS XML ON;
GO
SELECT * FROM SALES.SALESORDERDETAIL
ORDER BY UnitPrice DESC;
GO

Screenshot showing DOP

I then changed the configuration option, and ran the same test.

EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE;
GO
SELECT * FROM SALES.SALESORDERDETAIL
ORDER BY UnitPrice DESC;
GO

This time, the degree of parallelism was 4, as expected.

I then added the MAXDOP hint, with a value smaller than 4 and then a value larger than 4.

SELECT * FROM SALES.SALESORDERDETAIL
ORDER BY UnitPrice DESC
OPTION (MAXDOP 2);
GO
SELECT * FROM SALES.SALESORDERDETAIL
ORDER BY UnitPrice DESC
OPTION (MAXDOP 6);
GO

In both cases, the degree of parallelism was the same as the value specified in the hint. So it seems, with this basic test, that a MAXDOP hint will completely override anything set in the configuration option. Whether this was true in the version when the hint was first introduced, I cannot say. But it’s true for me now, and that’s what’s important.

So I’ll clean up and then go back to playing with Hekaton.

EXEC sp_configure 'max degree of parallelism', 0; RECONFIGURE;
GO
SET STATISTICS XML OFF;
GO

Have fun!

~Kalen

Published Tuesday, June 03, 2014 1:30 PM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

This is how it's been since 2005.

2008 docs:

http://msdn.microsoft.com/en-us/library/ms181714(v=sql.100).aspx

Overrides the max degree of parallelism configuration option of sp_configure and Resource Governor for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure. If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see Configure the max degree of parallelism Server Configuration Option.

2005 had similar wording (minus the resource governor bits of course):

http://msdn.microsoft.com/en-us/library/ms181714(v=sql.90).aspx

Overrides the max degree of parallelism configuration option of sp_configure for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure. All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see max degree of parallelism Option.

2005 was the first time you could specify a MAXDOP *higher* than the sp_configure setting; as described in this KB article, 2000 ignored the local hint if it was higher thna sp_configure:

http://support.microsoft.com/kb/2806535

June 3, 2014 5:51 PM
 

Kalen Delaney said:

Thanks, Aaron. I know it was documented this way, but as we know, things don't always work exactly the way they are documented. I have a definite recollection that that there was some fuzziness somewhere, which is why I decided to do this simple test on the current versions.

June 3, 2014 6:07 PM
 

Steve Hood said:

Setting MaxDOP = 0 isn't cleaning up, it's making a mess!!!  :-)

June 3, 2014 8:29 PM
 

Aaron Morelli said:

Whenever I can't remember the logic for determining DOP, I find this CSS post helpful (though the gory details were a bit intimidating for me at first):

http://blogs.msdn.com/b/psssql/archive/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop.aspx

June 3, 2014 8:57 PM
 

Greg Linwood said:

Setting the server wide setting to 1 and then using the query hint to elevate MAXDOP where needed for specific queries is a very good starting point for most OLTP systems & we frequently use this approach to bring OLTP systems with excessive parallelism under control, particularly more recently since licensing costs have been tied to CPU cores & avoiding wastage of CPU resource has become a higher priority

June 4, 2014 10:59 AM
 

Kalen Delaney said:

Steve... interesting point, although of course, it depends! If I was really creating a distributable script, I should have captured the initial value of 'max degree of parallelism', and reset to that value when I was done. I am just setting back to the default here.

AaronM... thanks for the link! I don't recall seeing that post... and of course it points out that I didn't take Workgroup settings through RG into account at all. It's a good thing I wasn't claiming this was a comprehensive analysis.

Greg! That is a very interesting idea. I usually tell people never to set 'max degree of parallelism' to 1 out of the box, but possibly to a very small value > 1 like, maybe, 2. As long as you have more than that number of cores. But this alternative is definitely something I might mention. Thanks!

June 4, 2014 1:24 PM
 

jchang said:

I would not recommend setting sys.configures max degree of parallelism to 1 even if certain queries are set specifically to a higher value.

I do agree with setting the system wide value low, but > 1.

It is not appreciated that certain query plans are actually more CPU efficient at DOP > 1. Not just run faster with lower elapsed time, but actually lower worker time. These usually have the bitmap operator in the parallel plan. There might be other operators that SQL Server does not consider when DOP is 1. I think Columnstore is another case, but I prefer to let Jimmy talk on this topic.

June 4, 2014 9:50 PM
 

Greg Linwood said:

Joe, there is a big difference between OLTP & Reporting DBs, are you referring to both or just OLTP or Reporting?

MyDBA has been measuring the performance of OLTP SQL Server systems commercially since 2002 & we have a significant body of empirical data that clearly shows setting server wide MAXDOP setting to 1 & incrementing it only where necessary works very well on most OLTP systems, across a wide body of industries & various commercial OLTP DBs.

As you say, there are cases where certain query plans are more efficient at DOP > 1 but the vast majority of OLTP queries are better suited to single CPU.

Reporting systems are different but on OLTP, the vast majority of queries are best suited to single CPU & exceptions can be configured with the query hint, above the server wide configuration as Kalen points out in this blog

June 5, 2014 1:01 AM
 

Adam Machanic said:

@Greg Have you also measured impact on DBCC performance?

A better option, IMO, than heavy tweaking of server-wide MAXDOP, is a combination of a higher Cost Threshold for Parallelism setting and utilization of Resource Governor.

June 5, 2014 9:44 AM
 

Greg Linwood said:

Adam - yes, we measure impact on DBCC performance & DBCC can obviously degrade without parallelism but if DBCC is being run when users are on the system, it is generally better to run it single threaded to control its CPU impact on user queries and when DBCC is being run in periods of low activity you can always alter MAXDOP just for that period or use RG as you pointed out, assuming Ent Edn is available which isn't always

However, as a wide generalisation about OLTP configuration of MAXDOP the reason most OLTPs are provisioned with multiple CPUs is to allow multi-user concurrency rather than individual queries to consume multiple CPUs. Therefore, leaving MAXDOP unconstrained on an OLTP is not a good idea and uncontrolled query parallelism is frequently a major performance problem we observe

Oracle has had this issue the right way around for a long time - query parallelism is not switched on (not even installed) by default and you must install / switch it on if you want it. This is most commonly done with Reporting servers for the reason explained above.

SQL Server is installed by default with uncontrolled query parallelism which basically means it is installed with Reporting system defaults rather than OLTP defaults & needs to be reconfigured for OLTP rather than for Reporting as with Oracle. As most SQL installs are OLTP & there are usually a number of OLTPs in most companies per Reporting system, I think Microsoft has this default configuration the wrong way around

June 5, 2014 11:07 AM
 

Adam Machanic said:

@Greg

I agree that the default configuration settings in this area are absolutely ridiculous. First thing I change on any production system.

However, it's been a long time since I've seen a true "pure" OLTP system where only very small queries are being run. I do think it's important to allow a bit of headroom for bigger reporting queries in the mixed workload environments that are commonplace today. Of course your argument will be -- I think -- that these will cause the OLTP queries to slow down if they're allowed to use too much of the available resources. So I think we're back to It Depends/YMMV :-)

June 5, 2014 1:25 PM
 

Kalen Delaney said:

This is an awesome discussion, thanks! I don't think Greg is recommending this as best practice in the general case. He is saying that he frequently does this on the systems that he manages (and tunes and tests and monitors). All best practices are just guidelines, and a place to start from for those who are just getting started. But the more you know SQL Server and the more you know your system, the more you can start to move away from the recommendations once you determine that is best for YOU. I've known Greg a long time, and I know that whatever he's doing, he's tested it thoroughly. This isn't the first time that Greg has said he does things that are different than standard best practices, but that's fine. He is not recommending that everyone just do the same.

June 5, 2014 2:45 PM
 

Rowland Gosling said:

My theory is to keep the max setting to about half of the available logical processors and then expect the developers to use the maxdop hint if their code isn't performing well due to parallel plans. That way code that benefits from parallelism is not affected.

June 9, 2014 3:40 PM
 

omega 3 max said:

Kalen Delaney : Geek City: A Hint of Degrees

October 29, 2014 4:58 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement