The Art of the Execution Plan

Execution Plans

There’s beauty everywhere – even in SQL Server.  I was just staring at this plan when I was struck by its beauty:

Stored Procedure Part 1

Oh, sure, it’s ugly, and I recoiled in horror at first, but when you get over that reaction, there’s an amazing amount of stuff there.  Someone – probably many someones – poured every waking moment of their life into building a query that does a staggering amount of stuff.  Then under that, SQL Server had to churn away to interpret all that text, look at the database, and decide how to execute this work.  All of that isn’t just man-hour – it’s man-decades, all distilled into one giant array of elbow lines.  One screen doesn’t even begin to capture it.  Let’s look at another view of that same plan:

Doing lines
Stored Procedure Part 2

It’s like a Mark Bradford piece, one of his giant collage mashups of aerial maps – you just can’t appreciate it until you see the plan in life size, scrolling around in all its sprawl. Once I saw execution plans as art, I went looking for pleasing patterns, and they were everywhere.

Parallel Parallelism

Repeated rows of parallel parallelism become little armies marching in formation.  Who cares if the work looks redundant?  Why tune the query?  Praise the developer for their artful design.

Plumbing the Depths

Lines of loops become fishermen casting their lines into the bottom of the execution plan, dragging up data from the deep.  The execution plan turns into a statement about religion: these fishers of data work diligently, raising up information to the highest power – the end user.

The Darkness Begins

Familiar enemies come to life as villains, bringing conflict to the art.  Curses, cursors!

Wiping the Slate Clean

Everyone wants a fresh start.  The artist tells of wiping the slate clean before starting anew, but thanks to the foreshadowing lines to the left, we all know how this will end – badly.  The strife begins again with each call of the query, struggling against itself.

Printing Pinstripes

Artists love to make prints, don’t they?  Some queries fancy themselves as artists, printing their own works of mysterious art.  What do they produce?  Why are the lines so long?  Seeing the plan through this small frozen window leaves the viewer wanting to know more.  Sometimes, though, you take one look at a plan and you don’t want to know any more whatsoever:

X Marks the Spot

Even SSMS can’t handle the zoom-out feature on this particular work of art.  It cries out for mercy with a blood-red X, signifying that it’s been wounded.  Everything is mortal, even software.  Ain’t art grand?

Previous Post
Notes on Scalability
Next Post
SQL Server Can Run Databases from Network Shares & NAS

24 Comments. Leave new

  • They say that art is anything you’re pointing at when you say “this is art”.
    In all seriousness, you’re not far off. Good art makes you think and gives your brain a good wake up jolt. This plan does that and I’ve seen stuff it galleries that doesn’t come close to this.

  • Query plans can be a be a beautiful thing man! Fun!

  • It’s beatiful except for the OPEN CURSOR part. 🙂

  • so now we have to figure out (or Michael does, guru that he is – how to paint a picture with an execution plan…

  • Brent,
    As a gallery owner, and artificial art official, I agree to the intrinsic beauty of what looks to be a complex query. This raises a question for me.
    I have created complex queries of layered SQL statement, but prefer to build longer more piecemeal scripts that are more maintainable, easier to read and to document. Also, I have found more opportunity for reuse.

    Is there any real advantage to building a complex query that does it all in on shot compared to a more sequential step-wise process that generate even less artsy query plans?



    • John – usually, I’d rather see step-wise processes for two reasons: easier restartability when one part of the process bombs, and easier development. It’s easier for me to hand a small stored proc or query to someone and say, “Tune this, and make sure it returns the same result set as the original” instead of a giant stored proc and have them tune just a small part of it.

  • Lazaro Fernandez
    January 5, 2012 9:52 am

    Me being the Happy DBA I find this execution plan hillarious…it is one of those moments you would like to take off running and have a drink….a big one!

  • From this piece, I take away both a feeling of awe and wonder at the complexity of things at both their micro and macro levels, as well as a feeling of sadness and futility at attempts to make sense of it all.

    That and it’s freakin’ hilarious. Thanks, Brent. 🙂

  • This is _hilarious_!

  • John Hennesey
    January 5, 2012 2:21 pm

    Aside from the obvious beauty and unbelievable complexity that goes into the visualization of the plans, I find myself asking:
    “How on earth did he think up this post? And how does he keep coming up with these ideas?”

    Very entertaining, and do share what you drink just before you have an inspiration… 🙂

  • Hmm, where have I seen that red X before? Must have been SSRS-related…

    And also: what John H. said! 🙂

  • Your text had me laughing! “…fishers of data..” I love it!

  • Awesome post Brent! Hopefully you will forgive us for stripping the native plans of most of this intrinsic “beauty” 😉

  • What a brilliant post!
    The best comedy has a grain of truth, and this has more than most

  • Estimated or Actual? Does it run? Will it blend? Is this monster scheduled to run at the same time as the nightly backups? Has IT resource governed it into a constrained box 🙂

    In conclusion, wow. And eek.

  • Brent, you need a holiday.
    Or was it having one that did it ?!
    It should be in The Tate Modern, whichever.

  • Lol, brilliant post!

    When in the trenches one rarely sits back and appreciate the beauty behind an execution plan. This could start a whole new trend of optimising your SQL for a better artistic appeal.

  • That’s a good one Brent, it made me laugh…I never looked at execution plans that way, lol, my favorite is the “X Marks the Spot”

  • Brent thats really nice thing you pointed out, i never see execution plans like this instead i just skip when i see this kind of execution plan

    great work

  • This explains what DBA really means: Database Artist!

    Next article: How to optimize your art using query hints.

  • The formality of lines is clearly a mark of your adherence to corporatism.

    I much prefer the jaggedness of a short sample cpu perfmon chart, the random evil edge.

  • Is it a new piece of work by Banksy….

  • “Call that a query plan? THIS is a query plan!”


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.