Muokkaa

Jaa

Display and save execution plans

  • Artikkeli

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This section explains how to display execution plans and how to save execution plans to a file in XML format by using SQL Server Management Studio (SSMS).

Note

For more information about viewing and saving plans in Azure Data Studio, see Query Plan Viewer in Azure Data Studio.

Execution plans graphically display the data retrieval methods chosen by the SQL Server Query Optimizer. Execution plans represent the execution cost of specific statements and queries in SQL Server using icons rather than the tabular representation produced by the SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT statements. This graphical approach is useful for understanding the performance characteristics of a query.

While the SQL Server Query Optimizer produces only one execution plan, there is the concept of estimated execution plan, an actual execution plan, and live query statistics.

  • An estimated execution plan returns the compiled plan as produced by the Query Optimizer, based on estimations. This is the query plan that is stored in the plan cache. Producing the estimated execution plan doesn't actually execute the query or batch, and therefore doesn't contain any runtime information, such as actual resource usage metrics or runtime warnings.

  • An actual execution plan returns the compiled plan plus its execution context. It becomes available after the query execution has completed. This plan includes actual runtime information such as execution warnings, and in newer versions of the Database Engine, the elapsed and CPU time used during execution.

  • Live query statistics return the compiled plan plus its execution context. This plan is available for in-flight query executions, and is updated every second. This includes runtime information such as the actual number of rows flowing through the operators, elapsed time, and the estimated query progress. This option isn't available in Azure Data Studio.

For more information on query execution plans, see the Query processing architecture guide.

Next steps