Execution plan overview
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
To be able to execute queries, the SQL Server Database Engine must analyze the statement to determine an efficient way to access the required data and process it. 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 Query Optimizer builds one or more query execution plans, sometimes referred to as query plans or execution plans. The Query Optimizer chooses a query plan using a set of heuristics to balance compilation time and plan optimality in order to find a good query plan.
Tip
For more information on query processing and query execution plans, see the sections Optimizing SELECT statements and Execution plan caching and reuse of the Query processing architecture guide.
For information on viewing execution plans in SQL Server Management Studio and Azure Data studio, see Display and save execution plans.
A query execution plan is the definition of:
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 accessTableA
, use the data fromTableA
to extract matching rows fromTableB
, and then use the data fromTableB
to extract data fromTableC
. 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's 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 small, table scans may be the most efficient method for almost all access to the table.The methods used to compute calculations, and how to filter, aggregate, and sort data from each table.
As data is accessed from tables, there are different methods to perform calculations over data such as computing scalar values, and to aggregate and sort data as defined in the query text, for example when using a
GROUP BY
orORDER BY
clause, and how to filter data, for example when using aWHERE
orHAVING
clause.
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
Next steps
Feedback
https://aka.ms/ContentUserFeedback.
În curând: Pe parcursul anului 2024, vom elimina treptat Probleme legate de GitHub ca mecanism de feedback pentru conținut și îl vom înlocui cu un nou sistem de feedback. Pentru mai multe informații, consultați:Trimiteți și vizualizați feedback pentru