To understand exactly what each operator does within an execution plan, you have to look at the properties of the operator. But there are also some properties that relate to the plan as a whole. They can normally be found on the root (top-left) operator. For execution plans that are captured from Extended Events or Profiler Trace, the top-left operator is not displayed. Some of the properties are still embedded in the plan (though only visible by examining the XML); others are not captured at all.
Note that Microsoft has been adding a lot of new properties to the top-left node during the last releases – even adding properties in Cumulative Updates (CUs). The list below is valid as of SQL Server 2017, CU12. It shows properties exposed in the graphical interface of SSMS version 17.9. I don’t check for properties that are visible in the execution plan XML only, but I do add these if I know about them.
If you are using an older version of SQL Server, some of these properties may not be available.
If you see any properties not included in this list, please let me know!
|Property name||XML element name||Description|
|Cached plan size||Cached plan size||The size (in KB) of the execution plan in the procedure cache. For plans not stored in the procedure cache, this represents the size the plan would take.|
|CardinalityEstimationModelVersion||CardinalityEstimationModelVersion||The version of the cardinality estimator that was used when optimizing this plan. Valid values are 70 (SQL Server 2005 through 2012, aka legacy cardinality estimator), 120 (SQL Server 2014), 130 (SQL Server 2016), and 140 (SQL Server 2017).|
|CompileCPU||CompileCPU||The CPU time (in milliseconds) used to compile (parse, bind, and optimize) the execution plan.|
|CompileMemory||CompileMemory||The memory (in KB) used to compile (parse, bind, and optimize) the execution plan.|
|CompileTime||CompileTime||The elapsed time (in milliseconds) used to compile (parse, bind, and optimize) the execution plan.|
|DatabaseContext-SettingsId||DatabaseContext-SettingsId||A reference to the context_settings_id column in the catalog view sys.query_context_settings. Only used when Query Store is enabled.|
|Degree of Parallelism||DegreeOfParallelism||The number of parallel nodes when executing the plan. Actual execution plan only.|
|Memory Grant||MemoryGrant||The amount of memory in KB that is estimated to be required for running the execution plan without having to spill to disk. This property is only included if the plan includes one or more of the specifically memory-hungry operators (Adaptive Join, Hash Match, Sort). In other cases the memory grant for the query is probably computed based on the number of operators and the degree of parallelism. Actual execution plan only.|
|MemoryGrantInfo||MemoryGrantInfo||Detailed information about the memory requirements of the query, broken down into components:
Most of these are only in actual execution plans.
|MissingIndexes||MissingIndexes||Lists one or more indexes that, if they had existed, would have enabled the optimizer to compile a more efficient plan. Each index is listed with an “Impact” value, that represents the expected percentage saving on the estimated total plan cost for this query.|
|NonParallelPlanReason||NonParallelPlanReason||Used when the optimizer tried to create a parallel plan but failed to created one.|
|Optimization Level||StatementOptmLevel||The type of optimization used when compiling this execution plan; can be TRIVIAL or FULL.|
|OptimizerHardware-DependentProperties||OptimizerHardware-DependentProperties||Various properties of the available hardware and system configuration that were taken into account when compiling the execution plan:
|OptimizerStatsUsage||OptimizerStatsUsage||Lists all the statistics that were used during the optimization of the execution plan, including their last update timestamp, sampling rate used during the last update, and number of modifications since the last update.|
|Parameter List||ParameterList||Lists all the parameters used by the query, with their data type, value at compile time (if the parameter was sniffed), and value at runtime (in an actual execution plan).|
|QueryHash||QueryHash||A hash value based on the query text, which can be used to find plans for similar or equal queries.|
|QueryPlanHash||QueryPlanHash||A hash value based on the execution plan, which can be used to find plans for similar or equal execution plans.|
|QueryTimeStats||QueryTimeStats||Reports the CPU time and elapsed time (in ms) used when running the execution plan. Actual execution plan only.|
|Reason For Early Termination Of Statement Optimization||StatementOptmEarlyAbortReason||Specifies the reason why the optimizer stopped optimizing and returned the plan used. The possible values are “Good Enough Plan Found” (the optimizer found a plan with a cost below the threshold for the current optimization phase), “Time Out” (the compile time surpassed the maximum optimize time set at the start of the optimization phase), and “Memory Limit Exceeded” (compilation had to be stopped due to memory shortage). This property is not included if the compilation process executes until the end of one of the phases and then determines that the next phase is not needed.|
|RetrievedFromCache||RetrievedFromCache||Shows whether or not the execution plan is stored in the procedure cache. The name of this property is confusing as it can be easily mistaken to mean that a plan was already in cache, and used without compilation. That is not the case; even when the query had to be optimized this property will still show true.
The only known cases where this property is false are:
|Set Options||StatementSetOptions||The setting of various SET options as they were when the query was compiled; these all have to match for the plan to be eligible for reuse. The settings included in this property are ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, and QUOTED_IDENTIFIER.|
|Statement||StatementText||The query text of the statement that created this execution plan, or the first 4000 characters of it if the text exceeds that length.|
|StatementParameterizationType||StatementParameterizationType||The kind of parameterization used when the query was optimized. 0 = None; 1 = Manual; 2 = Simple; and 3 = Forced. Only included if the Query Store is enabled.|
|StatementSqlHandle||StatementSqlHandle||A reference to the StatementSqlHandle of the statement in the Query Store. Only included if the Query Store is enabled.|
|ThreadStat||ThreadStat||Detailed information about the threads used while running the execution plan. Not included in a serial plan.
In an estimated execution plan, only the Branches sub-property is included.
|TraceFlags||TraceFlags||Lists the trace flags that are in effect for the query. In an actual execution plan this property lists two sets of trace flags: those that were active when the query was compiled (IsCompileTime = true), and those that were active during execution (IsCompileTime = false).|
|WaitStats||WaitStats||Lists the ten highest wait stats that occurred during execution of the plan, broken down by category. For each category, the amount of waits (WaitCount) and the total wait time in milliseconds (WaitTimeMs) is listed. Actual execution plan only.|
|Warnings||Warnings||Lists all warnings that were generated during compilation of the execution plan. In an actual plan, additional run-time warning may be added.|