This month, Rob Farley (Blog | @Rob_Farley) is running T-SQL Tuesday on plan operators.  T-SQL Tuesday is a monthly blogging event where a number of us in the SQL Server community gets together to blog on a central topic.  As mentioned, the topic is plan operators, or as Rob puts it:

The topic is Plan Operators. If you ever write T-SQL, you will almost certainly have looked at execution plans (if you haven’t, go look at some now. I mean really – you should be looking at this stuff). As you look at these things, you will almost certainly have had your interest piqued by some, and tried to figure out a bit more about what’s going on.

That’s what I want you to write about! One (or more) plan operators that you looked into. It could be a particular aspect of a plan operator, or you could do a deep dive and tell us everything you know. You could relate a tuning story if you want, or it could be completely academic. Don’t just quote Books Online at me, explain what the operator means to you. You could explore the Compute Scalar operator, or the many-to-many feature of a Merge Join. The Sequence Project, or the Lazy Spool. You’re bound to have researched one of them at some point (if you never have, take the opportunity this week), and have some wisdom to impart. This is a chance to raise the collective understanding about execution plans!

Evil Plan operators

I’m sure you’ve heard one or more plan operators being considered evil from time to time.  Maybe your server has high CXPACKET waits and everyone cringes at the even mention of a parallel operator in the execution plan.  Then again, maybe one day a poorly performing query brought down your environment and the offending operation in the query plan was a table spool due to multiple DISTINCT counts.  Or, that one time at band camp, when numerous clustered index scans caused you to have to cancel Friday night plans so that you could resolve deadlock issues on the table.  In each of these cases, it wouldn’t be too surprising to find out that you consider the related plan operators to be evil.

But are they really evil?  Did Microsoft fill SQL Server with a number of poorly performing plan operators just to mess with us?  Are there truly any bad plan operators?  Would you be surprised to find out that there are no bad plan operators?

It’s true, every plan operator within SQL Server serves a purpose and that purpose helps you.  Parallel operations help when you have high volume data operations and need to leverage multiple CPUs to improve the performance of the query.  Clustered index scans occur when scanning the entire index is expected to consume less IO than seek operations or when a filter is unsupported by another index on the table.  And spool operations prevent multiple full scans of tables when subsets stored in tempdb can suffice.  All of these operations help to improve the performance of your query.

Unfortunately, an overabundance of these operations occurring at the same time can lead to contention issues; which in turn provides stress for the DBA.  But is aiming that stress towards the operators the best plan?  Do you assume these operators, which are meant to help, are the problem?  Or is it something else?

Is There A Misunderstanding?

The issue here is considering specific plan operators to be evil.  No plan operators are evil.  If not, though, then are your queries evil?  No, your queries are performing the tasks you’ve asked them to.  If it isn’t the plan operators or the queries, then maybe the indexes are evil.  If you don’t have the right indexes in place, that must be evil.  No, again, that isn’t evil, that’s just unfortunate.

Don’t look at your plan operators, queries, or indexes as being evil.  Instead, look to find the places where performance is suffering and work to mitigate that performance issue.  It might be that you can improve performance by applying indexing or rewriting the query.  Which may affect the plan operators that are used.  The key thing to remember is that while some performance issues may have common plan operators, the existence of a plan operator is not indicative of a problem.

Conclusion

So I kind of went on a tangent from Rob’s question today, but I think it’s an important thing to understand about plan operators.  The worst, in your mind, plan operators are really just misunderstood, kind of like teenagers.  It’s not that they mean to do bad, or harm, to your environment.  They are just doing the best job they can with the task that’s been assigned to them.  Instead of being critical of the plan operator, look first to the query and be sure that your request to SQL Server is clear and that the objects to support the request are in place.