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: Total Paralysis

As I wrote that title, which was supposed to be a cute reference to parallelization, I realized it could also be the title for a blog post about my most recent trip, with delays, cancellations, missed connections and lost luggage both coming and going. But that will be another blog post that I will have to write about later, because the trip isn't even over yet.  I am enroute from Philadelphia to Denver, 6 miles up, while I am writing this.

Parallelization is a frequently discussed topic when dealing with SQL Server Performance, and one of the most heretofore confusing issues is relationship between the configuration option 'max degree of parallelism' and the query hint MAXDOP. Neither of these options forces SQL Server to use a specific number of processors, unless of course you set either value to 1. Any value greater than one just gives SQL Server an upper limit, but during execution, a smaller number can be used depending on the existing workload.

The confusion I want to discuss now is what does the MAXDOP hint really allow? MAXDOP can be used in both queries and index building operations to set the upper limit on processor use, but I had received conflicting reports from various people about whether the MAXDOP hint could exceed the configuration option or whether or it could be used to only limit it still further.  The last response I had received from my contacts at Microsoft had indicates that using the MAXDOP hint would not allow SQL Server to exceed the serverwide configuration value, i.e. the MAXDOP hint could be used to set a lower limit for processor use but it could never increase your SQL Server's configured upper limit.

One of my students last week wanted to figure out a way to not allow any queries to run in parallel, but to allow parallelism for index builds. If hints couldn't be used to exceed the serverwide 'max degree of parallelism', they would have to configure the server for a high DOP, and then hint every single query for MAXDOP 1;  and this solution was not acceptable. Of course I agreed that it was unacceptable, so I thought I would revisit the question. I sent another email off to my favorite MS contacts, and while waiting for a response, I decided to test it out myself.

Although my laptop only has a single CPU, there is a traceflag you can use to simulate multiple CPUs. Using the SQL Server Configuration Manager, you can add the startup parameter -P#, where # is the number of processors you want to simulate, and SQL Server will create that number of schedulers. I usually run my machine with -P4.  I used a script provided by my friend Lara Rubbelke, a performance consultant with Digineer, in Minneapolis. It uses a database called Performance created by Itzik Ben-Gan that you can download from his site at www.insidetsql.com.

SQL Server 2005 provides a way for you to observe the number of CPUs used during execution when you look at an actual plan instead of an estimated plan. Actual plans, produced by SET STATISTICS PROFILE ON or SET STATISTICS XML ON, involve executing the query, while estimated plans, produced by the SET SHOWPLAN options, do not run the query. If you look at the actual graphical execution plan you can also see the number of processors actually used by looking at the details for the very first (leftmost) icon in the query plan. You can enable this option with Cntl-K.

So I set my serverwide setting to a maximum of 2 CPUs:

EXEC sp_configure 'max degree of parallelism', 2
RECONFIGURE

and then I ran this query batch:

SET STATISTICS XML ON
GO
USE Performance
GO
SELECT COUNT(*), ShipperID, EmpID
FROM Orders o INNER JOIN Customers c
ON o.custid = c.custid
WHERE c.custname LIKE 'Cust_1008%'
GROUP BY ShipperID, EmpID
OPTION (MAXDOP 4)
GO

I saw the following details in my XML plan output:

<QueryPlan DegreeOfParallelism="4"  .......

I then tried building an index and specifying MAXDOP:

CREATE INDEX IX_custid ON Orders(custid)
WITH (MAXDOP = 4)
GO

I again saw the actual number of processors used was 4.

I finally got the 'official' answer from Microsoft, after my test was completed, which verified what I had observed. Here is the algorithm used to determine the Degree of Parallelism (DOP) used at runtime to execute your query:

1. Set DOP to the value of the MAXDOP query_hint if there is one, otherwise set the DOP to the 'max degree of parallelism' value.
2. Set DOP to the minimum from (DOP, # of schedulers).
3. Adjust DOP due to workload;  it is possible to use a lower DOP if your server is loaded, i.e. there are not enough threads to run the query in the specified DOP. In the worst case SQL Server will run the query in serial.

Basically the same algorithm is also used for index builds.

Now back to the blog title... the engineer at Microsoft who developed the original parallel query implementation was not a native English speaker. When he talked about this great new feature he had developed, he sometimes wanted to use the word 'parallel' as a verb, and in his great enthusiasm he would sometimes leave off a syllable. So instead of saying 'We are going to be able to PARALLELIZE your queries', he would say 'We are going to be able to PARALYZE your queries'!

Have fun!

~Kalen

Published Sunday, July 29, 2007 7:14 AM by Kalen Delaney

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

 

Lara Rubbelke said:

Oh, this is just delightful!  I love the play on words - or perhaps in some cases it is not a play on words but an accurate representation of what happens when too many statements are running on multiple processors!  

July 29, 2007 6:51 PM
 

James Luetkehoelter said:

Heck, I have a hard time not saying PARALYZE instead of Parallelize or Paralllelism. I usually sound like Porky Pig and then just say "more than one processor". Have you run into any issues under 2005 yet that actually do paralyze the query as it at times happened in 7.0 or 2000 (where you have to specify MAXDOP=1)? Everyone keeps asking me if I've seen any instances of yet - which I have not.

July 29, 2007 8:48 PM
 

Kalen Delaney said:

Hi James

I haven't directly seen any paralysis on any of my clients systems on SQL 2005, but I have heard from students that this still does happen occasionally. But they agree that it happens a LOT less often than on SQL 2000.

~Kalen

July 30, 2007 12:31 PM
 

Kalen Delaney said:

Most of you who read my writings know that I don't often write about hardware. And in fact, I'm not writing

January 5, 2008 6:27 PM
 

Harman Sahni said:

Greetings Kalen,

Good job (and thanks for sharing) in getting an official answer from Microsoft on parallelism algorithm!

I've had numerous close encounter experiences with parallelism where I've seen enterprise level production servers getting paralyzed.

Having said that, albeit this is a known fact that the MDOP can be overridden by specifying the MAXDOP hint (http://technet.microsoft.com/en-us/library/ms188611.aspx), I was under an impression that if the MAXDOP hint value is greater than the value configured at the server level MDOP setting then the query hint value is simply ignored.... and this official verdict from Microsoft contradicts my thought.

I've now found the yet another official verdict from Microsoft which confirms my aforementioned thoughts:

http://technet.microsoft.com/en-us/library/ms181714.aspx

By the way, I've read numerous of your writings and have found them to be great. With due respect to this one as well, I'm not doubting yours but Microsoft's verdict!

February 26, 2008 7:12 PM
 

Harman Sahni said:

Additional 1/2 cent to my above post:

If the actual algorithm is that you can override the server setting value with an exceeding query hint value then this could be taken as a flaw as well as that would mean a developer is allowed to consume more resources than what a DBA has setup at a server level.

By the way, I hope the new "Resource Governor" feature in SQL 2008 will clear things out anyways!

February 26, 2008 7:18 PM
 

Adam said:

Kalen,

I thought you could provide a bit of clarity?

Recently I was given an urgent problem with a query a report writer had written. The query firstly populated a temp table and then proceded to populate a new temp table joining the 1st one to some physical tables.

The problem was that the query used to run in around 20 mins before all of a sudden never completing.

The challenge for me was that due to the time constraints involved I couldn't go through change management to make any physical changes to the database structure itself. I was therefore left to tune the query.

I did the obvious things first like removing order by's on the insert into table statment and returning only the columns used from derived tables instead of select *.

What I noticed though, that when I initially executed the query the optimiser was running the query in parallel and some threads were blocking each other on the same query - all relating to the same SPID. I remembered I had heard about this before and limiting the query to use 1 processor (using MAXDOP=1) can in cases allow a query to perform more quickly.

I added this hint and hey presto, it now runs in 7 minutes. What I really don't understand is HOW?

How was SQL Server blocking it's own threads within the same query?

How does preventing parallel executing, in some cases, increase performance?

Thank you

March 30, 2010 8:39 AM
 

Kalen Delaney said:

Hi Adam

You might get more interesting discussion by posting this on one of the free public help forums, rather than as a comment on a 2-year old blog post. My understanding is that when this self-blocking happens it is a bug. And to get around the bug, you need to decrease the MAXDOP.

March 31, 2010 6:39 PM
 

Gaby A. said:

I know I may be way late for this, but is the sequence used applicable to SQL 2000?  We had a weird issue occur, and apparently it's a known (and irreparable) bug from Microsoft (we're up to date on our patching), but one option to drop max degree of parallelism to 1.  I heard it may make our integrity checks and optimizations take longer (which is fine in this case) but make our queries go much quicker, which is causing these messages to appear in our SQL and Windows Logs, EXCEPTION_ACCESS_VIOLATION and m_activeSdesList.Head () == NULL.

The Sysadmins have checked to verify it is not memory and we're currently failed over to a secondary node, just in case.

Thanks.

May 10, 2010 11:12 AM
 

Kalen Delaney said:

Hi Gaby

I am not sure what 'sequence' you are referring to. I didn't learn about either the traceflag or the indicator of the actual number of CPUs until SQL 2005. I don't know whether they existed in 2000 because I never tried them, and I don't have a 2000 installation now to test them on. Do you? This is something you could easily see for yourself.

~Kalen

May 11, 2010 2:29 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