Execution Plans

APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

To be able to execute queries, the SQL Server Database Engine must analyze the statement to determine the most efficient way to access the required data. This analysis is handled by a component called the Query Optimizer. The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan or just execution plan.

A query execution plan is a definition of the following:

  • The sequence in which the source tables are accessed.
    Typically, there are many sequences in which the database server can access the base tables to build the result set. For example, if a SELECT statement references three tables, the database server could first access TableA, use the data from TableA to extract matching rows from TableB, and then use the data from TableB to extract data from TableC. The other sequences in which the database server could access the tables are:
    TableC, TableB, TableA, or
    TableB, TableA, TableC, or
    TableB, TableC, TableA, or
    TableC, TableA, TableB

  • The methods used to extract data from each table.
    Generally, there are different methods for accessing the data in each table. If only a few rows with specific key values are required, the database server can use an index. If all the rows in the table are required, the database server can ignore the indexes and perform a table scan. If all the rows in a table are required but there is an index whose key columns are in an ORDER BY, performing an index scan instead of a table scan may save a separate sort of the result set. If a table is very small, table scans may be the most efficient method for almost all access to the table.

Tip

For more information on query processing and query execution plans, see the Query Processing Architecture Guide.

In This Section

See Also

Monitor and Tune for Performance
Performance Monitoring and Tuning Tools
Query Processing Architecture Guide
Live Query Statistics
Activity Monitor
Monitoring Performance by Using the Query Store
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Trace flags
Showplan Logical and Physical Operators Reference