Execution Plan Analysis: The Mystery Work Table

re: Execution Plan Analysis: The Mystery Work Table

mjswart — Thu, 07 Mar 2013 22:16:52 GMT

Well done Paul, I always like these in depth posts of yours. The time spent running through the examples always pays off.

There's a lot of things going on here to make this issue occur. There's the nested loops prefetching, the manufactured lobs etc...

I like to put myself in the position of a DBA who's asked to look at this query. To be honest I don't think I'd be up to the task of understanding the exact cause without a humungous amount of mental effort (so good on you).

But I like to think that maybe some knee-jerk rules of thumb I exercise might have done the trick: Concatenating strings in the app for example.

But thanks again for the write up! Your internals posts are always fascinating.

re: Execution Plan Analysis: The Mystery Work Table

Alejandro Mesa — Thu, 07 Mar 2013 22:22:31 GMT

A delightful reading!

Adam, could you add the "Like" button?

AMB

re: Execution Plan Analysis: The Mystery Work Table

Paul White — Thu, 07 Mar 2013 22:50:21 GMT

Hi Michael,

I agree, though in this particular case, it turns out the string concatenation was to feed input to an in-query CLR function - a valid use case with only partial workarounds and alternatives AFAIK. Nevertheless, this was a fun post to write.

Paul

P.S. Cheers, Alejandro!

re: Execution Plan Analysis: The Mystery Work Table

SomewhereSomehow — Mon, 11 Mar 2013 05:37:14 GMT

Very interesting detective story from the one hand, and useful informative reading from the other, thank you!

Small question: "the logical reads reported on this work table indicate the number of rows" - curious note! Is it documented somewhere, or just an observation?

re: Execution Plan Analysis: The Mystery Work Table

Paul White — Mon, 11 Mar 2013 06:21:07 GMT

Hi Dima,

More details at http://stackoverflow.com/questions/4230838/why-are-logical-reads-for-windowed-aggregate-functions-so-high and http://sqlblog.com/blogs/paul_white/archive/2010/07/28/partitioning-and-the-common-subexpression-spool.aspx

As far as being documented or not - depends: it is mentioned in a book somewhere - I think the author might have been Craig Freedman, I'm afraid I don't recall more than that.

re: Execution Plan Analysis: The Mystery Work Table

SomewhereSomehow — Mon, 11 Mar 2013 08:40:10 GMT

Anyway, that's interesting. Thanks for the links!

re: Execution Plan Analysis: The Mystery Work Table

John Waclawski — Thu, 04 Apr 2013 13:09:01 GMT

Great, great information!! Thank you!!

Is there anyway to repost this information not using SQL Sentry because some of us can't afford to buy the software & would rather use what is available to us. Unless the free version gives us the same information.

Thank you again!!

re: Execution Plan Analysis: The Mystery Work Table

Paul White — Wed, 17 Apr 2013 23:37:21 GMT

Hi John,

The free version is all that is required to reproduce the screenshots in this post. I do prefer to use P/E for blog work because if nothing else the graphical plans fit better in the space available. As you can see, there are other benefits in the way it presents information too.

I do offer a full refund to anyone not happy with the way I write my blog posts of course :-p