Query Tuning Recommendations

Some queries consume more resources than others. For example, queries that return large result sets and those that contain WHERE clauses that are not unique are always resource intensive. No degree of query optimizer intelligence can eliminate the resource cost of these constructs when compared to a less complex query. SQL Server uses the optimal access plan, but query optimization is limited by what is possible.

Nonetheless, to improve query performance, you can:

• Add more memory. This solution can be especially helpful if the server runs many complex queries and several of the queries execute slowly.

• Use more than one processor. Multiple processors allow the SQL Server Database Engine to make use of parallel queries. For more information, see Parallel Query Processing.

• Rewrite the query. Consider the following issues:

  • If the query uses cursors, determine if the cursor query could be written using either a more efficient cursor type (such as fast forward-only) or a single query. Single queries typically outperform cursor operations. Because a set of cursor statements is typically an outer loop operation, in which each row in the outer loop is processed once using an inner statement, consider using either a GROUP BY or CASE statement or a subquery instead. For more information, see Cursor Types (Database Engine) and Query Fundamentals.

  • If an application uses a loop, consider putting the loop inside the query. Often an application contains a loop that contains a parameterized query, which is executed many times and requires a network round trip between the computer running the application and SQL Server. Instead, create a single, more complex query using a temporary table. Only one network round trip is necessary, and the query optimizer can better optimize the single query. For more information, see Procedural Transact-SQL and Transact-SQL Variables.

  • Do not use multiple aliases for a single table in the same query to simulate index intersection. This is no longer necessary because SQL Server automatically considers index intersection and can make use of multiple indexes on the same table in the same query. Consider the sample query:

    SELECT * FROM lineitem 
    WHERE partkey BETWEEN 17000 AND 17100 AND
        shipdate BETWEEN '1/1/1994' AND '1/31/1994'
    

    SQL Server can exploit indexes on both the partkey and shipdate columns, and then perform a hash match between the two subsets to obtain the index intersection.

  • Make use of query hints only if necessary. Queries using hints executed against earlier versions of SQL Server should be tested without the hints specified. The hints can prevent the query optimizer from choosing a better execution plan. For more information, see SELECT (Transact-SQL).

• Make use of the query governor configuration option. The query governor configuration option can be used to prevent system resources from being consumed by long-running queries. By default, the option is set to allow all queries to execute, no matter how long they take. However, you can set the query governor to limit the maximum number of seconds that all queries are allowed to execute for all connections, or just the queries for a specific connection. Because the query governor is based on estimated query cost, rather than actual elapsed time, it does not have any run-time overhead. It also stops long-running queries before they start, rather than running them until some predefined limit is hit. For more information, see query governor cost limit Option and SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL).

• Optimize reuse of query plans from the plan cache. SQL Server Database Engine caches query plans for possible reuse. If a query plan is not cached, it can never be reused. Instead, uncached query plans must be compiled each time they are executed, which results in poorer performance. The following Transact-SQL SET statement options prevent cached query plans from being reused. A Transact-SQL batch that contains these SET options turned ON cannot share its query plans with the same batch that was compiled with these SET options turned OFF:

  SET ANSI_NULL_DFLT_OFF	SET ANSI_NULL_DFLT_ON
  SET ANSI_NULLS	SET ANSI_PADDING
  SET ANSI_WARNINGS	SET ARITHABORT
  SET CONCAT_NULL_YIELDS_NULL	SET DATEFIRST
  SET DATEFORMAT	SET FORCEPLAN
  SET LANGUAGE	SET NO_BROWSETABLE
  SET NUMERIC_ROUNDABORT	SET QUOTED_IDENTIFIER
  SET TEXTSIZE

  In addition, the SET ANSI_DEFAULTS option affects the reuse of cached query plans because it can be used to change the ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS, and the QUOTED_IDENTIFIER SET options. Note that most of the SET options that can be changed with SET ANSI_DEFAULTS are listed as SET options that can affect the reuse of query plans.
  You can change some of these SET options with the following methods:

  • Use the sp_configure stored procedure for server-wide changes. For more information, see sp_configure (Transact-SQL).
  • Use the SET clause of the ALTER DATABASE statement. For more information, see ALTER DATABASE (Transact-SQL)
  • Change OLE DB and ODBC connection settings. For more information, see Client Network Configuration.

See Also

Concepts

Subquery Fundamentals
GROUP BY Components

Other Resources

CASE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance