THE SQL Server Blog Spot on the Web

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

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server) and leader of the SQL User Group in Adelaide, Australia. Rob is also a Director of PASS, and runs training courses around the world in SQL Server and BI topics.

Plan Operator Tuesday round-up

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

I put them together and made the following clickable plan. It’s 1000px wide, so I hope you have a monitor wide enough.

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

Published Thursday, June 13, 2013 9:00 AM by Rob Farley
Filed under:

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

 

Adam Machanic said:

Fantastic work, Rob!

June 13, 2013 10:18 AM
 

Airborne Geek said:

"Hopefully that’s not what Tamera planned, but anyway..."

HAHAHAHAHAHA, you are my hero.

June 13, 2013 11:01 AM
 

Jason Brimhall said:

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.

June 13, 2013 11:27 AM
 

Wayne Sheffield said:

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...)

June 13, 2013 11:42 AM
 

Mickey Stuewe said:

That is the coolest post. Thanks for hosting.

--Mickey

June 13, 2013 1:14 PM
 

John Alan said:

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

June 13, 2013 1:25 PM
 

Argenis Fernandez said:

OMG BWAHAHAHAHAHAHA

June 14, 2013 1:57 AM
 

Chris Yates said:

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.

July 2, 2013 10:43 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement