Plan properties
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, which often will be the Result operator (labeled as SELECT in the execution plan) or the Language Element operator (labeled as INSERT, UPDATE, DELETE, or MERGE). For execution plans that are captured from Extended Events or Profiler Trace, this 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.
Property list
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 contains properties I know of, as of SQL Server 2022, as far as they are exposed in the graphical interface of SSMS version 20.2. 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 |
|---|---|---|
| BatchModeOnRowStoreUsed | BatchModeOnRowStoreUsed | If a query uses no columnstore indexes but the optimizer made a heuristics based decision to run in Batch mode, this property is included and set to True. Otherwise, the property is not included. Introduced in SQL Server 2019. |
| 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), 140 (SQL Server 2017), 150 (SQL Server 2019), and 160 (SQL Server 2022). |
| 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. |
| ContainsInterleavedExecutionCandidates | ContainsInterleavedExecutionCandidates | If the execution plan contains at least one operator that qualifies for interleaved execution, this property is included and set to True. There is no information exposed in an execution plan only to see which operator(s) trigger this interleaved execution. In an execution plan plus run-time statistics, you can look for operators with the IsInterleavedExecuted property. As far as known, this only happens on Table Valued Function operators in all current versions of SQL Server. When the execution plan does not qualify for interleaved execution, then this property is omitted. This property is only included on newly generated execution plans. It never appears on execution plans that are taken from the plan cache. When the database compatibility level is at 130 (SQL Server 2016) or lower, this property can still be included in the execution plan, but it will not affect execution. Introduced in SQL Server 2017. |
| 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. Introduced in SQL Server 2016. |
| Degree of Parallelism | DegreeOfParallelism | The number of parallel nodes when executing the plan. Only available in execution plan plus run-time statistics. |
| Description | n/a | This property can be found in SQL Server Management Studio only. It provides a short description of what each operator does. This data is completely provided by SQL Server Management Studio itself, it is not actually part of the execution plan XML. |
| Dispatcher | Dispatcher | If Parameter Sensitive Plan optimization was used, then this property contains one or more ParameterSensitivePredicate elements (displayed as just a number in the graphical execution plan, or not shown at all if there is just one), that each contain these sub-properties:
Introduced in SQL Server 2022. |
| 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. Only available in execution plan plus run-time statistics. |
| MemoryGrantInfo | MemoryGrantInfo | Detailed information about the memory requirements of the query, broken down into components:
|
| 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 create one. Microsoft has documented most values this property can take here. |
| Operation Type | OperationType | In an execution plan only (“estimated execution plan”) for a DECLARE CURSOR statement, the Operation Type differentiates the two sub-plans (“operations”) of the cursor plan. The possible values are PopulateQuery and FetchQuery. Only available in execution plan only, and only for cursor operations. |
| 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 execution plan plus run-time statistics). |
| ParentObjectId | ParentObjectId | Currently unknown. |
| PlanGuideDB | PlanGuideDB | If a plan guide was successfully applied to the query, then this property lists the database where the plan guide is defined. |
| PlanGuideName | PlanGuideName | If a plan guide was successfully applied to the query, then this property lists the name of the plan guide. |
| 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. |
| QueryStoreStatementHintId | QueryStoreStatementHintId | If a Query Store hint was successfully applied when compiling the query, then this property holds the query_hint_id (as shown in sys.query_store_query_hints). Introduced in SQL Server 2022. |
| QueryStoreStatementHintSource | QueryStoreStatementHintSource | If a Query Store hint was successfully applied when compiling the query, then this property shows whether the hint was user-supplied or system-generated. Introduced in SQL Server 2022. |
| QueryStoreStatementHintText | QueryStoreStatementHintText | If a Query Store hint was successfully applied when compiling the query, then this property shows the text of the hint. Note that this overrides the query hint, if any, in the original query text. Introduced in SQL Server 2022. |
| QueryTimeStats | QueryTimeStats | Detailed information on elapsed time and CPU time used when running the execution plan.
Introduced in SQL Server 2016. |
| QueryVariantID | QueryVariantID | If Parameter Sensitive Plan optimization was used, then this property shows which plan variant was used. The QueryVariantID is an integer value. If the Dispatcher property contains just a single Predicate, then the variant is 1 for the low cardinality variant, 2 for medium cardinality, and 3 for high cardinality. If there are two Predicates, then the value for the first Predicate is multiplied by 3 and then added to the value for the second Predicate. When there are three Predicates, then the first value is multiplied by 9, the second by 3 and the third is added without multiplication. Introduced in SQL Server 2022. |
| 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:
|
| SecurityPolicyApplied | SecurityPolicyApplied | When set to True, one or more of the objects used in the query have a security policy for row-level security, which has resulted in extra operators being added to the execution plan. Introduced in SQL Server 2016. |
| 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. If the statement contains any elements that might contain embedded passwords, then the entire query text is replaced with the constant “** Restricted Text **”. |
| 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. |
| (displayed as operator name in the graphical execution plan) | StatementType | The type of statement executed. This is usually equal to the first keyword of the query, or the first keyword after all Common Table Expressions, for instance SELECT, UPDATE, ALTER DATABASE. There are also a few special cases, such as ASSIGN for both SET statements and assignment in the DECLARE, or SELECT WITHOUT QUERY for very simple queries that can be executed without execution plan. This is a regular element in the execution plan XML, but the graphical execution plan shows this property as the name of the top-left operator, and not as a regular property. |
| ThreadStat | ThreadStat | Detailed information about the threads used while running the execution plan. Click here more detailed information. Not included in a serial plan.
|
| 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 (in an execution plan plus run-time statistics) those that were active during execution (IsCompileTime = False). Introduced in SQL Server 2014. |
| UnmatchedIndexes | UnmatchedIndexes | This property shows a list of filtered indexes that were considered by the optimizer, but rejected because of a mismatch between the filter condition of the index and the predicate required for the query. If this property is present, then there is always also a Warnings property with subproperty UnmatchedIndexes set to True. (Note that this warning is not properly displayed in the properties popup in SSMS). |
| Use plan | UsePlan | This property is present and set to True if a USE PLAN query hint was successfully applied to the query. This includes literal usage of OPTION (USE PLAN …) in the query, forcing a plan through use of a plan guide, or forcing a plan through the Query Store. |
| 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. Only available in execution plan plus run-time statistics. Introduced in SQL Server 2016. |
| Warnings | Warnings | Lists all warnings that were generated during compilation of the execution plan. In an execution plan plus run-time statistics, additional run-time warnings may be added. |