Query Performance

Before a Transact-SQL query, batch, stored procedure, trigger, prepared statement, or dynamic SQL statement begins execution on an instance of Microsoft SQL Server, it is compiled into a query plan by the SQL Server Database Engine query optimizer. Then the plan is executed to produce a result-set. The compiled query plans are stored into a part of SQL Server memory that is called the plan cache. Query performance can be analyzed to improve it by viewing query execution plans or by manipulating the way cached query plans are used by applications. The topics in this section contain information about how you can view and manipulate SQL Server Database Engine query plans to improve query performance. 

In This Section

• Query Tuning
  Contains information about how to use the Showplan feature of the SQL Server Database Engine to view and analyze query plans.

• Optimizing Queries in Deployed Applications by Using Plan Guides
  Contains information about using plan guides to optimize query performance when changing the text of the query is not possible.

• Specifying Query Plans with Plan Forcing
  Contains information about using the USE PLAN query hint to force the query optimizer to use a specific query plan for a query.

• Finding and Tuning Similar Queries by Using Query and Query Plan Hashes
  Describes using the query hash and query plan hash values in dynamic management views to help find and enhance the performance for similar queries that collectively consume significant system resources.

• Optimizing Queries That Access Correlated datetime Columns
  Describes using the DATE_CORRELATION_OPTIMIZATION database SET option to improve query performance for queries that perform an equi-join between two tables whose datetime columns are correlated.

• Using the FORCESEEK Table Hint
  Describes using the FORCESEEK table hint to force the query optimizer to use only an index seek operation as the access path to the data in the specified table or view.

• Optimizing MERGE Statement Performance
  Provides best practice recommendations to help you achieve optimal performance when using the MERGE statement.

• Optimizing Data Warehouse Query Performance Through Bitmap Filtering
  Describes how the query optimizer can create efficient query plans for queries against star schemas by using bitmap filtering.

• Query Performance How-to Topics
  Provides step-by-step instructions for performing query performance related procedures.