Display and Save Execution Plans

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

This section explains how to display execution plans and how to save execution plans to a file in XML format by using Microsoft SQL Server Management 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 very 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 and actual execution plan.

  • An estimated execution plan returns the execution plan as produced by the Query Optimizer at compile-time. Producing the estimated execution plan does not actually execute the query or batch, and therefore does not contain any runtime information, such as actual resource usage metrics or runtime warnings.
  • An actual execution plan returns the execution plan as produced by the Query Optimizer, and after queries or batches finish execution. This includes runtime information about resource usage metrics and any runtime warnings.

    For more information, see Query Processing Architecture Guide.

In This Section