Plan Operator Tuesday round-up

June 13, 2013

Eighteen posts for T-SQL Tuesday #43 this month, discussing Plan Operators.

I put them together and made the following clickable plan.

Let me explain this plan for you (people’s names are the links to the articles on their blogs – the same links as in the plan above).

It was clearly a SELECT statement. Wayne Sheffield (@dbawayne) wrote about that, so we start with a SELECT physical operator, leveraging the logical operator Wayne Sheffield.

The SELECT operator calls the Paul White operator, discussed by Jason Brimhall (@sqlrnnr) in his post. The Paul White operator is quite remarkable, and can consume three streams of data. Let’s look at those streams.

The first pulls data from a Table Scan – Boris Hristov (@borishristov)’s post – using parallel threads (Bradley Ball@sqlballs) that pull the data eagerly through a Table Spool (Oliver Asmus@oliverasmus). A scalar operation is also performed on it, thanks to Jeffrey Verheul (@devjef)’s Compute Scalar operator.

The second stream of data applies Evil (I figured that must mean a procedural TVF, but could’ve been anything), courtesy of Jason Strate (@stratesql). It performs this Evil on the merging of parallel streams (Steve Jones@way0utwest), which suck data out of a Switch (Paul White@sql_kiwi). This Switch operator is consuming data from up to four lookups, thanks to Kalen Delaney (@sqlqueen), Rick Krueger (@dataogre), Mickey Stuewe (@sqlmickey) and Kathi Kellenberger (@auntkathi). Unfortunately Kathi’s name is a bit long and has been truncated, just like in real plans.

The last stream performs a join of two others via a Nested Loop (Matan Yungman@matanyungman). One pulls data from a Spool (my post@rob_farley) populated from a Table Scan (Jon Morisi). The other applies a catchall operator (the catchall is because Tamera Clark (@tameraclark) didn’t specify any particular operator, and a catchall is what gets shown when SSMS doesn’t know what to show. Surprisingly, it’s showing the yellow one, which is about cursors. Hopefully that’s not what Tamera planned, but anyway…) to the output from an Index Seek operator (Sebastian Meine@sqlity).

Lastly, I think everyone put in 110% effort, so that’s what all the operators cost. That didn’t leave anything for me, unfortunately, but that’s okay. Also, because he decided to use the Paul White operator, Jason Brimhall gets 0%, and his 110% was given to Paul’s Switch operator post.

I hope you’ve enjoyed this T-SQL Tuesday, and have learned something extra about Plan Operators. Keep your eye out for next month’s one by watching the Twitter Hashtag #tsql2sday, and why not contribute a post to the party? Big thanks to Adam Machanic as usual for starting all this.

@rob_farley

This Post Has 9 Comments

  1. Adam Machanic

    Fantastic work, Rob!

  2. Airborne Geek

    "Hopefully that’s not what Tamera planned, but anyway…"
    HAHAHAHAHAHA, you are my hero.

  3. Jason Brimhall

    Excellent Work.  The Execution Plan is awesome.
    I’ll give 110% of my work to that magical operator, no problem!!
    This was a fun one.
    Thanks for hosting.

  4. Wayne Sheffield

    I think Rob just set a new standard for a TSQL-Tuesday round-up post.
    (But I do wonder what it would have looked like if it incorporated all of the operators that were posted about. Hmmm…)

  5. Mickey Stuewe

    That is the coolest post. Thanks for hosting.
    –Mickey

  6. John Alan

    Viewed through Plan Explorer this plan reveals a lot more detail!

  7. Argenis Fernandez

    OMG BWAHAHAHAHAHAHA

  8. Chris Yates

    Hi Rob, I was unable to participate but wanted to ensure that I read through everyone’s input. This post was about the best I’ve seen, kudos for taking the time to do it and appreciative for all you do with the community.

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.

Search