THE SQL Server Blog Spot on the Web

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

Page Free Space

See also my SQL Server technical articles on SQLperformance.com

Myth: SQL Server Caches a Serial Plan with every Parallel Plan

Published Thursday, November 4, 2010 10:23 PM by Paul White

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

 

Pradeep Singh said:

Hi Paul, This is really amazing stuff you have posted. Thanks for putting in so much effort.

November 4, 2010 6:27 AM
 

Pradeep Adiga said:

Thanks Paul for the very useful post!

November 4, 2010 6:35 AM
 

Paul White said:

Hi Pradeep (and Pradeep!)

Thank you both very much (especially Pradeep Singh for the 'retweet').

It was a lot of effort, but I enjoyed researching and writing this entry very much.  Glad you liked it.

Paul

November 4, 2010 7:19 AM
 

Matt Whitfield said:

Wow - that is an awesome post.

I am going to have to read that several times before it sinks in.

Top job sir.

November 4, 2010 10:53 AM
 

Robert L Davis said:

Excellent post, as always!! There was indeed a lot of confusion around this when Conor Cunningham visited our MCM rotation. According to Conor, SQL Server generates 2 plans (assuming it isn't a trivial plan), but only caches the plan that ends up being used based on whether or not the costing determines that parallelism can be used. And even if the parallel plan is generated, the engine isn't locked into performing it in parallel. Like you said, it may cache the parallel plan, and the engine may strip out the parallelism at run time and execute it serially.

November 4, 2010 10:56 AM
 

Paul White said:

Matt,

Thank you - it's always pleasing to find people I 'know' reading my blog.  Glad you enjoyed it first time through.

I agree there is a lot to think about.  I originally planned this as a relatively short entry showing a parallel plan executing on one thread, but it kinda ran away with me from there.

Thanks again for taking the time to leave a comment.  It's very encouraging.

Paul

November 4, 2010 11:11 AM
 

Paul White said:

Robert,

Many thanks to you also - kinda humbling getting such great feedback from a member of the exclusive MCM club!  The compilation/optimization process is a truly fascinating area, and there are no shortage of myths to be found there.  That gives me an idea... :c)

Cheers,

Paul

November 4, 2010 11:19 AM
 

Robert L Davis said:

No offense intended to my fellow MCMCs, but frankly, a lot of what you post is above the level of many MCMs.

November 4, 2010 11:29 AM
 

Alexander Kuznetsov said:

Hey Paul,

This is extremely interesting, keep it up! You should write a book.

November 4, 2010 11:38 AM
 

Paul White said:

Robert,

Then I shall try a second time to compliment you by calling you a famous author instead! :c)

Paul

November 4, 2010 12:20 PM
 

Sandeep Kalidindi said:

Paul - Thanks for this really informative and well explained post. good work!  

November 4, 2010 2:25 PM
 

ALZDBA said:

As usual ... Very, very .... nice, informing, illustrated and correct !

November 5, 2010 2:50 AM
 

Paul White said:

Alex,

Glad you enjoyed it.  A book you say?  What an interesting idea ;c)

Paul

November 5, 2010 11:48 AM
 

Paul White said:

Sandeep & Johan (ALZDBA):  Thanks guys!

November 5, 2010 11:52 AM
 

GrumpyOldDBA said:

I can't recollect this type of information ever being made available for SQL2008 or SQL 2005 and is what I might have expected to be found in various "internals" books. I agree that you should consider a book - I'd happily pay for this type of in depth stuff - it must take you some considerable time and I for one appreciate your time and effort.. Thanks.

November 8, 2010 9:43 AM
 

Paul White said:

Hi Colin (Grumpy Old DBA),

First - thank you *very* much for you comments.

Yes, as far as I am aware, the information in this post isn't available anywhere else.  By the way, I tried to leave an answer to your latest blog post (about memory) but keep getting an error page whenever I try to log in - same with Dave Ballentyne's blog.  I wonder if sqlblogcasts is blocking connections from New Zealand?

Anyway, I almost always recommend setting locked pages on x64, but it is important to set *max* (not min!) server memory in that case.  You'll find lots of good, and accurate, information here:

http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

Paul

November 8, 2010 10:52 AM
 

csm said:

Fantastic job, Paul. All of your posts must be readed carefully and added to the favorites links (in fact, this is what I always do)

And if you write this book (please, e-book), here is another reader

November 17, 2010 6:53 AM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement