Seven Showplan Red Flags

Do you use Query Analyzer's Graphical Showplan, SHOWPLAN_ALL, or SHOWPLAN_TEXT to performance tune your query statements? Most DBAs use one or all of these tools for troubleshooting query performance.

But what do most DBAs look for when they examine the query execution plan results? Ask fifty DBAs, and chances are you'll get fifty different answers.

Most DBAs and Developers don't have all day to muck around in large execution plans, and yet it is easy to get lost and distracted by the results of a complicated query execution plan. Although SQL Query issues are like snowflakes (very few are exactly alike), we still need to apply troubleshooting practices as consistently as possible.

This article will review some of the major areas to pay attention to when analyzing SHOWPLAN output. Since I use Graphical Showplan the most, I will speak to these "red flags" from that tool's perspective.

Two caveats:

First, this isn't an exhaustive list by any means, as performance issues can arise from thousands of reasons, each with different resolutions.

Secondly, many of the "red flags" documented here are a often "green flags" depending on your particular query and situation. Think of this list as an adaptive action plan, where you traverse the various red flags to rule-in or rule-out the culprit.

For those who are unfamiliar with SHOWPLAN, either visual or tabular, I suggest you review the SQL Server Books Online topics "SET SHOWPLAN_ALL", "SET SHOWPLAN_TEXT", and "Graphically Displaying the Execution Plan Using SQL Query Analyzer".

In a single statement query, begin by searching for the operator with the highest percentage cost. Searching for the highest cost operator allows you to prioritize which problems must be addressed first.



Remember to read the graphical execution plan output from right to left and from top to bottom.

If you are tuning a multi-statement query, remember that a separate graphical execution plan is created for each statement. Each graphical execution plan will show the ordinal position of the query, for example "Query 1", "Query 2"... Search for the query with the highest query cost, and then drill down on highest cost operator.

Scans (Table , Index ,and Clustered Index ) are operations that search all rows from the table and return those rows that satisfy the WHERE clause (assuming you use a WHERE clause).

A Table Scan retrieves the rows from a non-indexed table (a "heap"). An Index Scan searches rows from a nonclustered index. A clustered Index Scan searches rows from the Clustered Index of a table.

Scans are not always bad. Scans of small tables are often not an issue, nor are table scans that must always return all rows.

Scans can be bad if your query runs too long for your liking. This, of course, depends on what you are trying to do, and how fast you are trying to do it.

Scans are often resolved by proper indexing. Indexing is a large topic all by itself, so the resolution depends on your situation.

The end goal is usually to make the query run faster. Some solutions include changing your query to be more selective, using WHERE clause operators that are more selective, adding/modifying/removing indexes, removing hints (query, table, or join), changing the actual table design, and using Index Tuning Wizard.

Warnings are indicated when a physical operator is displayed in red. Warnings can indicate, for example, missing column statistics for a table. Warnings should be addressed immediately and appropriately (depending on the warning type) with either the creation of statistics, indexes, or the addition of JOIN clauses.

Arrows are not operators, but rather instead used to connect operators to each other. The number of rows impacted by the source operator determines thickness of the arrow. By placing your cursor over the arrow, you can display a row count and row size.

Focus your attention on thicker arrows, as larger row sets could indicate higher I/O activity.

To reduce the number of rows returned, make sure you are using the WHERE clause to narrow down your result set. Avoid retrieving more rows than necessary.

Hash joins are usually chosen by the query optimizer as a last resort.



Loop Joins are considered first if one join input is small and the other join input is large. Loop Joins require that both inputs (columns) must be indexed.

A merge join may be considered next by the optimizer, if the two join inputs are both sorted on the join column.

Hash joins, considered last, can be the most resource intensive of the three join operations, and therefore should be paid attention to.

If you encounter high cost hash operations, see if you are able to add/modify indexes so that they are used by the query.

Hash joins are not always inappropriate, and can perform better than other join operations, depending on the query and database schema.

Bookmark Lookup operators occur in conjunction with a nonclustered index seek, when the query must retrieve from columns that are not available within the nonclustered index.



The Bookmark Lookup operation references a row ID or clustering key in order to look up the corresponding row in the table or clustered index.

Look for high percentage costs (relative to total statement cost) on the Bookmark Lookup operator.

If the cost percentage of the Bookmark Lookup operation is high, investigate whether an appropriate clustered index can be used instead of the original row retrieval operation, or a non-clustered index with the appropriate columns added (a covering index).

A Sort operator orders all incoming rows in either an ascending or descending order, depending on the ORDER BY clause of your query.

Sort operators typically add I/O, primarily using the tempdb database for its operations.

If you are sorting your results with an ORDER BY unnecessarily, and the SORT operator has a high cost within a long running query, consider removing the ORDER BY clause.

Otherwise, if you know you will be sorting frequently on a specific column, consider indexing it. (Remember that you can fix the ascending or descending sort direction for a particular index column in the CREATE INDEX command).