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" 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
- Start SQL Query Analyzer, connect to the server, and select the database that you are working on.
- Paste the query into the SQL Query Analyzer window.
- 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.
- 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.
- 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.
- 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.
Before Optimize Query "write your query so you can read it... if you can't read it how can you possibly optimize it?" @Jacob H Thanks.
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.