How To: Optimize SQL Queries

Improving .NET Application Performance and Scalability

J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman
Microsoft Corporation

May 2004

Related Links

Home Page for Improving .NET Application Performance and Scalability

Chapter 12, Improving ADO.NET Performance

Chapter 14, Improving SQL Server Performance

Checklist: ADO.NET Performance

Checklist: SQL Server Performance

Send feedback to Scale@microsoft.com

patterns & practices Library

Summary: 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.

Contents

Summary of Steps
Step 1. Isolate Long-Running Queries
Step 2. Identify the Cause of Long-Running Queries
Additional Resources

Applies To

• Microsoft® SQL Server™ 2000

Overview

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:

1. Isolate long-running queries.
2. 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" in the "How To" section of this guide.

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 https://msdn.microsoft.com/en-us/library/aa178423(SQL.80).aspx.

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.

Additional Resources

For more information, see the following resources:

• Chapter 14, "Improving SQL Server Performance"
• "Checklist: SQL Server Performance" in the "Checklists" section of this guide
• "How To: Optimize SQL Indexes"
• Microsoft Knowledge Base article 243589, HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later, at https://support.microsoft.com/default.aspx?scid=kb;en-us;243589

© Microsoft Corporation. All rights reserved.