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 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.
Use query parameterization to allow reuse of cached query execution plans. If a set of queries has the same query hash and query plan hash, you might improve performance by creating one parameterized query. Calling one query with parameters instead of multiple queries with literal values allows reuse of the cached query execution plan. For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes and Execution Plan Caching and Reuse.
If you can not modify the application, you can use template plan guides with forced parameterization to achieve a similar result. For more information, see Specifying Query Parameterization Behavior by Using Plan Guides.
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).
Use the query_plan_hash to capture, store, and compare the query execution plans for queries over time. For example, after changing the system configuration, you can compare query plan hash values for mission critical queries to their original query plan hash values. Differences in query plan hash values can tell you if the system configuration change resulted in updated query execution plans for important queries. You might also decide to stop execution for a current long-running query if its query plan hash in sys.dm_exec_requests differs from its baseline query plan hash, which is known to have good performance. For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes.
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. The 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.
Note
To avoid recompilations of query plans caused by SET options, establish SET options at connection time, and make sure that they do not change for the duration of the connection. Some SET options must be set to specific values to use indexed views or indexes on computed columns. For more information, see SET Options That Affect Results.