How To Optimize SQL Queries
- J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman
You can use SQL Query Analyzer to examine the query execution plan of Transact-SQL (T-SQL) queries. This How To describes how to optimize T-SQL queries by using SQL Query Analyzer, and discusses how to analyze the individual steps contained in an execution plan.
- Microsoft® SQL Server™ 2000
To most effectively optimize queries, you should start by identifying the queries that have the longest duration. You can do so by using SQL Profiler. Next, you analyze the queries to determine where they are spending their time and whether they can be improved. You can use the SQL Query Analyzer to help analyze query behavior.
Summary of Steps
The overall optimization process consists of two main steps:
- Isolate long-running queries.
- Identify the cause of long-running queries.
Step 1. Isolate Long-Running Queries
You can isolate long-running queries using SQL Profiler. For more information about how to identify the queries that take the longest to execute, see "Isolating a Slow-Running Query with SQL Profiler" in How To Use SQL Profiler.
Step 2. Identify the Cause of Long-Running Queries
Several techniques can be used to identify the cause of long-running queries. The two most commonly used options are:
- Using SET statements.
- Using SQL Query Analyzer options.
- Using SET Statements
Use such statements as SET SHOWPLAN_ALL, SET STATISTICS IO, SET STATISTICS TIME, and SET STATISTICS PROFILE. For more information about using these SET statements, see the SQL Server product documentation.
Using SQL Query Analyzer
SQL Query Analyzer displays query execution plans in text mode or graphical mode.
To use SQL Query Analyzer
1. Start SQL Query Analyzer, connect to the server, and select the database that you are working on.
2. Paste the query into the SQL Query Analyzer window.
3. If you are using SQL Profiler to trace queries, the query text can be copied from the trace window and used within SQL Query Analyzer.
4. On the Query menu, click Display Estimated Execution Plan. The estimated execution plan for the query is displayed. If the query window contains multiple queries, an execution plan is displayed for each query.
5. On the Query menu, click Show Execution Plan, and then run the query in the query window. Execution plans and query results now appear in separate panes of the window so you can view them together.
Figure 1 shows an example of an execution plan along with the related query information.
Figure 1: Query execution plan
6. Place the mouse pointer over any icon displayed in the query execution plan. Details of the query step are displayed, including information about the execution and cost of the step, as shown in Figure 2.
Figure 2: Query execution details
Analyzing the Results
The icons in the query window graphically represent each step in the execution plan. To read an execution plan, read from right to left and from bottom to top. To fully understand an execution plan, you need to familiarize yourself with the various icons that can be displayed. For more information, see "Graphically Displaying the Execution Plan Using SQL Query Analyzer" in SQL Server books online at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_5pde.asp.
The complexity of the results can vary depending on the nature of the query. The following list identifies some common things to look for:
- Red icons and warning messages. Look for icons that are color-coded red, and for warning messages. You might see a warning message such as "Warning: Statistics missing for the table." If the Physical operation in the query step details is in red, then it indicates that the query optimizer has chosen a less efficient query plan. The graphical execution plan suggests remedial action for improving performance. In the case of missing statistics you can right-click the icon, and click Manage Statistics to create the missing statistics.
- Estimated cost. The estimated cost values indicate whether the query is I/O intensive or CPU intensive.
- Table scan and clustered index scan icons. Look for table scan and clustered index scan icons, which indicate either that the table is small (not a problem), that the indexes are not properly designed, or, if you have indexes in place, that the optimizer has ignored the indexes. The Index Tuning Wizard can be used to identify the indexes needed. You need to ensure that you drop all hints before proceeding with the Index Tuning Wizard.
- Queries with the highest cost. When a batch of queries is executed, a query plan is displayed for each query. The query cost is displayed for each query relative to the batch. Concentrating optimization effort on the highest relative cost query in a batch may yield the best improvements.
Some queries are inherently resource intensive — for example, queries that return large number of rows back to the caller, or queries that perform many calculations. In some cases, the only way to improve performance is to redesign the database or rewrite the query.
For more information, see the following resources:
- Microsoft Knowledge Base article 243589, HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later, at http://support.microsoft.com/default.aspx?scid=kb;en-us;243589