T-SQL Tuesday #43 – Plan Operators

This month’s TSQL-Tuesday is hosted by Rob Farley (blog | twitter), who became a Microsoft Certified Master this past December (congrats!). 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 we’ve looked into. With this guideline in mind, I’ve decided to write about:

Performance affecting plan operators

 (Table-Valued Function)

One of the operators that I look for in execution plans is the Table-Valued Function (TVF) operator. This operator represents a T-SQL Multi-Statement TVF or a CLR TVF. In either case, when the function is called, the results are stored in tempdb, and then returned from tempdb to the query. So, right off, we are incurring IO operations (definitely logical, probably physical also) in tempdb.

However, this isn’t why I go looking for this operator. When I see this operator in an execution plan, I know that I’m not seeing the entire picture. With this operator, all we know from looking at the execution plan is that “something” is going on, but we don’t know what that “something” is or what its true cost is. (I did a deeper dive into this in a previous blog post: Comparing Inline and Multi-Statement Table-Valued Functions.) What gets me the most is that SQL “sees” these operators with an absurdly low cost (normally 0%). Since they are based on Table Variables, SQL also “sees” an estimated number of rows of just 1 row. Having this Table Valued Function present in the execution plan means that the query plan chosen frequently has room for improvement. If you can convert the Multi-Statement TVF into an Inline TVF, SQL will be able to consider everything going on and will frequently produce a better performing query plan.

/ (RID Lookup / Key Lookup)

The RID and Key Lookup Operators are a bookmark lookup (you might remember the bookmark lookup operator from SQL 2000), in which a lookup is being performed into a table based upon either the Row Identifier (RID) or the Clustered Index Key. What this means is that the query has decided to utilize a non-clustered index on a table, but that index doesn’t completely cover the query (meaning that there are columns utilized in the query that are not in the index). Therefore, SQL has to take the RID (or Clustered Index key value(s)) from the non-clustered index, and perform an additional lookup operation into the table for that row to get the extra columns needed for the query. This lookup operation is performed for each row being returned. When seeing this plan operator in an execution plan, I see the potential opportunity for indexing tuning and/or query tuning to improve the performance of the query.

Seeing the Table Scan operator in a query plan also means that there is a table that we should consider adding a clustered index to.

/ (Table Scan / Clustered Index Scan)

The Table Scan and Clustered Index Scan operators tell me that SQL has decided that it needs to scan an entire table. The difference between these two operators is that the Table Scan is against a heap, while a Clustered Index Scan is against a table with a Clustered Index. Normally, when you see either of these operators, you have a query that is scanning the entire table. When seeing these operators, I’m again seeing indexing and query tuning opportunities. However, keep in mind that SQL can handle some small tables more efficiently with a Table (or Clustered Index) Scan than with other operators.

/ (Select)

The Select operator (referenced in BOL as a Result operator) is one that is present in all query plans for select statements. It’s also the easiest one to find – it will always be at the top left. And most people never really look at this operator – yet this operator can tell you so much about why the query plan is performing badly. That little exclamation mark in the yellow triangle tells me that there is something in this query that generated a warning. (In the case of this contrived query, it is an implicit conversion – knowing this means that I will probably see either a table scan, clustered index scan or index scan somewhere in the execution plan.) However, this operator is loaded with other information. By checking the properties, I can determine whether SQL was able to generate the best plan possible for this query, or whether it settled for one that was “good enough”… or even if it took the one with the least cost out of the combination of plans that it had looked for before it was forced to make a choice based upon time (a “timeout”). It can tell you the set options in effect for this query (well, not all – just the ones that can have an effect on how a query runs). It can even tell you the parameters that were supplied for this query, and the parameters that this query plan was initially designed for (which can help you troubleshoot parameter sniffing issues). When you are examining query plans, this is one that you don’t want to ignore.

Wrap-up

I’ve presented to you here the Plan Operators that I initially look for when I’m looking at a query for performance issues. Finding these may lead me to tweak indexes, or to perform coding changes to the query.

Thanks for this subject Rob. I’m looking forward to see what everyone else has to say about it.