Query Tuning Recommendations

Some queries are inherently resource intensive. This is related to fundamental database and index issues. These queries are not inefficient, because the query optimizer will implement the queries in the most efficient fashion possible. However, they are resource intensive, and the set-oriented nature of Transact-SQL can make them appear inefficient. No degree of query optimizer intelligence can eliminate the inherent resource cost of these constructs. They are intrinsically costly when compared to a less complex query. Although Microsoft® SQL Server™ will use the most optimal access plan, this is limited by what is fundamentally possible. For example, the following types of queries can be resource intensive:

• Queries returning large result sets
• Highly nonunique WHERE clauses

However, recommendations for tuning queries and improving query performance include:

• Add more memory (especially if the server runs many complex queries and several of the queries execute slowly).
• Run SQL Server on a computer with more than one processor. Multiple processors allow SQL Server to make use of parallel queries. For more information, see Parallel Query Processing.
• Consider rewriting the query.
  • If the query uses cursors, determine if the cursor query could be written more efficiently 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.
  • If an application uses a loop, consider putting the loop inside the query. Often an application will contain 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.
  • 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. For example, given 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.
• Make use of the query governor configuration option and setting. The query governor configuration option can be used to prevent long-running queries from executing, thus preventing system resources from being consumed. By default, the query governor configuration option allows all queries to execute, no matter how long they take. However, the query governor can be set to the maximum number of seconds that all queries for all connections, or just the queries for a specific connection, are allowed to execute. 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.

See Also