SQL Server 2000 Performance Guidelines - Execution Plans

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman


Evaluate the Query Execution Plan

In SQL Query Analyzer, enable the Display Execution Plan option, and run your query against a representative data load to see the plan that is created by the optimizer for the query. Evaluate this plan, and then identify any good indexes that the optimizer could use. Also, identify the part of your query that takes the longest time to run and that might be better optimized. Understanding the actual plan that runs is the first step toward optimizing a query. As with indexing, it takes time and knowledge of your system to be able to identify the best plan.

Avoid Table and Index Scans

Table and index scans are expensive operations, and they become more expensive as data grows. Investigate every table or index scan that you see in an execution plan. Can an index be created that would allow a seek operation instead of a table scan? Eliminating unnecessary I/O caused by scans is one of the quickest ways to obtain a substantial improvement in performance.

Not all table or index scans are bad. The optimizer selects a scan for tables that have fewer than a few hundred rows, and a clustered index scan may be the most effective option for some queries. However, you generally should avoid scans.

Evaluate Hash Joins

Make sure that you investigate hash joins in a query execution plan. A hash join may be the best option, but frequently, a hash join is selected because there are no indexes that the optimizer can use to perform an efficient nested loop or merge join. In the absence of indexes, a hash join is the best option. However, better indexing may occur from a nested loop or merge join. Note that hash joins are also fairly CPU intensive. If you have high CPU usage, and you do not feel that enough work is being performed against the server to justify this, evaluate the execution plans by using SQL Profiler to find out if you have a lot of hash joins.

Queries that use a parallel execution plan often have to perform hash joins to recombine the finished parallel streams. Hash joins in this scenario are usually optimal and should not be a concern.

Evaluate Bookmarks

A bookmark in an execution plan indicates that an index was used to limit the table and that a bookmark was then used to probe the clustered index or the heap table to retrieve more data that is not available in the index. A bookmark is often used in this way to retrieve columns that are in a SELECT clause. This means that at least twice the I/O is necessary to retrieve the results.

A bookmark is not always a problem, but you should find out if adding a covering index might be more effective. A bookmark may not be a problem if the original index was very selective, in which case few bookmark lookups are needed. However, a bookmark to the data from an index that was not very selective would be problematic, especially if the resulting table rows spread across a significant percentage of the pages in the table.

Evaluate Sorts and Filters

Sorts and filters are both CPU intensive and memory intensive because the server performs these operations in memory. When there are instances of sorts and filters, find out if you can create an index that would support the sorting or the filtering. Filtering is often the result of an implicit conversion, so investigate the filter to learn if a conversion occurred. Sorts and filters are not always bad, but they are key indicators of potential problems, and you should investigate them further.

Compare Actual vs. Estimated Rows and Executions

When you read the output from a SHOWPLAN statement, start from the most-indented row that has the highest incremental change in the TotalSubtreeCost column. Carefully evaluate both the index selection and the optimizer's estimate by using the SET STATISTICS PROFILE ON command. This command runs the statement, so only use it on SELECT statements or T-SQL code that does not modify data, or you can preface the command with a BEGIN TRAN /ROLLBACK statement.

As an alternative, use the new profiler Performance:Showplan Statistics event in SQL 2000. This event belongs to event class 98. This event reports four columns that show estimated and actual rows and executions. You must select the Binary Data column before the profiler event adds data to the T-SQL or SP:stmtcompleted events.

Substantial differences in the estimated row count may indicate the optimizer had out-of-date statistics or skewed statistics. For example, if the estimated row count is 2 rows, and the actual row count is 50,000, the optimizer may have had out-of-date statistics or skewed statistics. Try using the UPDATE STATISTICS WITH FULLSCAN command.


Personal tools