This month’s TSQL-Tuesday is hosted by Rob Farley (blog | twitter), who became a Microsoft Certified Master for SQL Server 2008 this past December (you know that elitist club that should wear Jedi robes to Summit 2013). As this month’s host, Rob has selected the topic of this month’s world-wide T-SQL Tuesday blogging party to be about Plan Operators. Specifically, one or more Plan Operators that have piqued your interest. With this guideline in mind, I have picked a few operators.  Some you may know and some that may be completely new to you.  Some are indicators of poor performance and some may be indicators of spectacular performance.

First things First

What is an operator?  Well for that, let’s just cheat a bit and go straight to the book definition on the topic.  From BOL we see the following:

Operators describe how SQL Server executes a query or a Data Manipulation Language (DML) statement. The query optimizer uses operators to build a query plan to create the result specified in the query, or to perform the operation specified in the DML statement. The query plan is a tree consisting of physical operators.

You can read more on what an operator is or does from this link.

Lesser Known Operators

In this group, I will cover some operators that go in largely in anonymity.  Let’s play a little game and see if you recognize any of these operators.  I will show you some operator icons and give a description.  You see if you can name the operator.  After all of these operators, I will give you the actual name.  No peeking – Honor System here!

1.  This operator consumes multiple streams and produces multiple streams of work.  Think of it as a workhorse and an indicator that a lot is getting accomplished.  Imagine being able to consume a ton of work and consequently be able to produce a lot of work or information.

2.  This operator indicates a repeating cycle.  In other words, it can be quite repetitive and it can be never-ending.

3.  This one is a bit more difficult.  This occurs when a suitable icon cannot be displayed. It could very well be that the suitable icon is NTSFW.  This potentially represents a problem, but does not always represent a problem.

4.  This operator represents the retrieval of something more.  Work is being done and a retrieval is required.  This could be done anywhere from one to thousands of times in a given work cycle.

5.  Related to the prior operator, this operator can be done multiple times within a work cycle.  This represents that data has to be retrieved again in some fashion.

So how well do you think you did?

These are all valid operator icons within graphical execution plans.  I am willing to bet you have had more frequent exposure to these operators than you may want to admit.  For instance, the first operator has a stigma about it in graphical execution plans that makes everybody think it is a really bad thing.  But I am here to tell you how wrong you are.

Let’s compare answers and see why you may be really wrong about that first operator.

If you answered “Repartition Streams” or “Parallelism” for operator number 1, you are wrong.  You see, this operator is actually the following.

If you don’t know who Paul White is and the work he has done to educate everybody on execution plans and operators, then you probably need to crawl out from under that rock.  Quite simply, Paul takes in so much information and puts out just as much information – but it is high quality information.  Go check out his stuff at his blog.

If you are 0 for 1, don’t feel bad.  That was a toughy.  I think they get a bit easier from here.

Next operator indicated a repeating cycle.  Who thinks this is a while operator based on the T-SQL While Loop?  If you notice, I was careful to bypass wording about language constructs.  In this case, it is actually the following.

If you run into this kind of operator, you have my deepest sympathies.  This is a purported Senior DBA with the same year of experience ten times.  This can often cause you quite a few gray hairs.  If you are running into this operator, you can certainly do for some optimization.

Are you 0 for 2 at this point?  That’s ok, I think you may be getting the gist of this game now.

Let’s move on to that third operator.  I am willing to be that 75% of the participants are really close on this one.  This is a cursor of sorts.  The orange color of the icon helps to give it away.  But, this cursor comes about from a workplace outage.  Let’s take a look at what this really is.

Sure we hate to let people into the production environment.  Sometimes they do stupid things and it breaks the production environment.  Then you have some unhappy DBA cursing up a storm as he fixes it and goes on his merry way.

Next is operator number four in this little quiz.  The hint on this one was that there was a fetch somewhere in the mix.  That should make it very easy at this juncture.

How often do you task your Jr DBA as the gopher of the team?  This is a critical operator within the team.  Many times the senior does not have time for various tasks and s/he must assign some things to others to get them accomplished.  Jr go fetch this.  Jr go do that.  Jr get the disk space reports.  This operator in an execution plan may indicate a potential tuning effort should be engaged.  But when we are talking about it as the Jr DBA operator, it is an operator indicating that we might be running at a higher efficiency level – if you are distributing tasks to him/her appropriately.

I hope if you are 0 for 4 at this point that you will get this one.  This is potentially the best operator I have in the bunch.

If you are a Senior DBA and are not involved with this operator at all, then turn in your keyfob and report to security.  This is absolutely critical.  Mentor the junior, repeat the cycle, and teach the junior some more.  This is definitely an iterative operation but is an absolute must in the DBA world.

As a Senior DBA, part of the job is to teach the junior and then to use the operator from item 4 and send them on their way to perform various tasks.  Sometimes, we lose track of this operator and become too wrapped up in our own set of tasks because we can just do it faster.  Teach the Jr and then maybe you will have more time for other projects in the future.

Joking Aside

There are several operators that I really like to dive into in SQL Server.  I won’t claim to know near as much as Paul White on the topic, but I enjoy it nonetheless.  There are three operators that I tend to enjoy bringing up in interviews and I have even started a series on them.  The last two articles in the series will be published soon.  Yes, I was a slacker and took a long time to finish parts two and three, but they are near completion on edits and ready to send to SSC.  You can read the first one here.

If you have any question about the use of Paul White as an Operator, he and I discussed it first and I received his blessing to proceed.