SQL Server Query Execution Plan Analysis:


If you see any of the following in an execution plan, you should consider them warning signs and investigate them for potential performance problems. Each of them are less than ideal from a performance perspective.

* Index or table scans: May indicate a need for better or additional indexes.

* Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.

* Filter: Remove any functions in the WHERE clause, don't include Views in your Transact-SQL code, may need additional indexes.

* Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?

It is not always possible to avoid these, but the more you can avoid them, the faster your performance will be. [7.0, 2000] Updated 8-5-2005

*****

If you have a stored procedure, or other batch Transact-SQL code that uses temp tables, you cannot use the "Display Estimated Execution Plan" option in the Query Analyzer to evaluate it. Instead, you must actually run the stored procedure or batch code. This is because when a query is run using the "Display Estimated Execution Plan" option, it is not really run, and temp tables are not created. Since they are not created, any references to them in the code will fail, which prevents an estimated execution plan from being created.

On the other hand, if you use a table variable (available in SQL Server 2000) instead of a temp table, you can use the "Display Estimated Execution Plan" option [7.0, 2000] Updated 8-5-2005

*****

If you have a very complex query you are analyzing in Query Analyzer as a graphical query execution plan, the resulting plan can be very difficult to view and analyze. You may find it easier to break down the query into its logical components, analyzing each component separately. [7.0, 2000] Updated 8-5-2005

*****

The results of a graphical query execution plan are not always easy to read and interpret. Keep the following in mind when viewing a graphical execution plan:

* In very complex query plans, the plan is divided into many parts, with each part, listed one on top of the other on the screen. Each part represents a separate process or step that the query optimizer had (has) to perform in order to get to the final results.

* Each of the execution plan steps is often broken down into smaller sub-steps. Unfortunately, you don't view the sub-steps from left to right, but from right to left. This means you must scroll to the far right of the graphical query plan to see where each step starts.

* Each of the sub-steps and steps is connected by an arrow, showing the path (order) taken of the query when it was executed.

* Eventually, all of the parts come together at the top left side of the screen.

* If you move your cursor above any of the steps or sub-steps, a pop-up windows is displayed, providing more detailed information about this particular step or sub-step.

* If you move your cursor over any of the arrows connecting the steps and sub-steps, you see a pop-up window showing how many records are being moved from one step or sub-step to another step or sub-step.

[7.0, 2000] Updated 8-5-2005

*****

The arrows that connect one icon to another in a graphical query plan have different thicknesses. The thickness of the arrow indicates the relative cost in the number of rows and row size of the data moving between each icon. The thicker the arrow, the more the relative cost is.

You can use this indicator as a quick gauge as to what is happening within the query plan of your query. You will want to pay extra attention to thick arrows in order to see how it affects the performance of your query. For example, thick lines should be at the right of the graphical execution plan, not the left. If you see them on the left, this could indicate that too many rows are being returned, and that the query execution plan is less than optimal. [7.0, 2000] Updated 12-10-2003

*****

In an execution plan, each part of it is assigned a percentage cost. This represents how much this part costs in regard to resource use, relative to the rest of the execution plan. When you analyze an execution plan, you should focus your efforts on those parts that have the largest percentage cost. This way, you focus your limited time on those areas that have the greatest potential for a return on your time investment. [7.0, 2000] Added 12-10-2003

*****

In an execution plan, you may have noticed that some parts of the plan are executed more than once. As part of your analysis of an execution plan, you should focus some of your time on any part that takes more than one execution, and see if there is any way to reduce the number of executions performed. The fewer executions that are performed, the faster the query will be executed. [7.0, 2000] Added 12-10-2003

*****

In an execution plan you will see references to I/O and CPU cost. These don't have a "real" meaning, such as representing the use of a specific amount of resources. These figures are used by the Query Optimizer to help it make the best decision. But there is one meaning you can associate with them, and that is that a smaller I/O or CPU cost uses less server resources than a higher I/O or CPU cost. [7.0, 2000] Added 12-10-2003

*****

When you examine a graphical SQL Server query execution plan, one of the more useful thing to look for is how indexes were used (if at all) by the query optimizer to retrieve data from tables from a given query. By finding out if an index was used, and how it was used, you can help determine if the current indexes are allowing the query to run as well as it possibly can.

When you place the cursor over a table name (and its icon) in a graphical execution plan, and display the pop-up window, you will see one of several messages. These messages tell you if and how an index was used to retrieve data from a table. They include:

* Table Scan: If you see this message, it means there was no clustered index on the table and that no index was used to look up the results. Literally, each row in the table being queried had to be examined. If a table is relatively small, table scans can be very fast, sometimes faster than using an index.

So the first thing you want to do, when you see that a table scan has been performed, is to see how many rows there are in the table. If there are not many, then a table scan may offer the best overall performance. But if this table is large, then a table scan will most likely take a long time to complete, and performance will suffer. In this case, you need to look into adding an appropriate index(s) to the table that the query can use.

Let's say that you have identified a query that uses a table scan, but you also discover that there is an appropriate nonclustered index, but it is not being used. What does that mean, and how come the index was not used? If the amount of data to be retrieved is large, relative to the size of the table, or if the data is not selective (which means that there are many rows with the same values in the same column), a table scan is often performed instead of an index seek because it is faster. For example, if a table has 10,000 rows, and the query returns 1,000 of them, then a table scan of a table with no clustered index will be faster than trying to use a non-clustered index. Or, if the table had 10,000 rows, and 1,000 of the rows have the same value in the same column (the column being used in the WHERE clause), a table scan is also faster than using a non-clustered index.

When you view the pop-up window when you move the cursor over a table in a graphical query plan, notice the "Estimated Row Count" number. This number is the query optimizer's best guess on how many rows will be retrieved. If a table scan was done, and this number is very high, this tells you that the table scan was done because a high number of records were returned, and that the query optimizer believed that it was faster to perform a table scan than use the available non-clustered index.

* Index Seek: When you see this, it means that the query optimizer used a non-clustered index on the table to look up the results. Performance is generally very quick, especially when few rows are returned.

* Clustered Index Seek: If you see this, this means that the query optimizer was able to use a clustered index on the table to look up the results, and performance is very quick. In fact, this is the fastest type of index lookup SQL Server can do.

* Clustered Index Scan: A clustered index scan is like a table scan, except that it is done on a table that has a clustered index. Like a regular table scan, a clustered index scan may indicate a performance problem. Generally, they occur for two different reasons. First, there may be too many rows to retrieve, relative to the total number of rows in the table. See the "Estimated Row Count" to verify this. Second, it may be due to the column queried in the WHERE clause may not be selective enough. In any event, a clustered index is generally faster than a standard table scan, as not all records in the table always have to be searched when a clustered index scan is run, unlike a standard table scan. Generally, the only thing you can do to change a clustered index scan to a clustered index seek is to rewrite the query so that it is more restrictive and fewer rows are returned.