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 is valid as of SQL Server 2022. It shows properties exposed in the graphical interface of SSMS version 19.1. 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 nameXML element nameDescription
BatchModeOnRowStoreUsedBatchModeOnRowStoreUsedIf 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 sizeCached plan sizeThe 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.
CardinalityEstimation­ModelVersionCardinalityEstimation­ModelVersionThe 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).
CompileCPUCompileCPUThe CPU time (in milliseconds) used to compile (parse, bind, and optimize) the execution plan.
CompileMemoryCompileMemoryThe memory (in KB) used to compile (parse, bind, and optimize) the execution plan.
CompileTimeCompileTimeThe elapsed time (in milliseconds) used to compile (parse, bind, and optimize) the execution plan.
ContainsInterleavedExecutionCandidatesContainsInterleavedExecutionCandidatesIf 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-SettingsIdDatabaseContext-SettingsIdA 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 ParallelismDegreeOfParallelismThe number of parallel nodes when executing the plan.
Only available in execution plan plus run-time statistics.
Memory GrantMemoryGrantThe 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.
MemoryGrantInfoMemoryGrantInfoDetailed information about the memory requirements of the query, broken down into components:
  • DesiredMemory: Amount of memory estimated to be needed to run the execution plan with the desired degree of parallelism and without spilling to disk. This should be equal to the Memory Grant property.
  • GrantedMemory: The actual amount of memory that was granted to the query.
  • GrantWaitTime: The time (in ms??) the query had to wait until the requested memory grant was made available.
  • IsMemoryGrantFeedbackAdusted: Shows how the Memory Grant Feedback feature affects the memory grant of the query. Valid values are “No: First Execution”, “No: Accurate Grant”, “No: Feedback disabled”, “Yes:Adjusting”, and “Yes: Stable”.
    Introduced in SQL Server 2019.
  • LastRequestedMemory: The actual amount of memory requested for the previous execution of the query; used for the Memory Grant Feedback feature.
  • MaxQueryMemory: The maximum amount of memory available to the query when it started running.
  • MaxUsedMemory: The amount of memory that was actually used during execution of the query.
  • RequestedMemory: The amount of memory that was actually requested when the query started running, based on RequiredMemory, DesiredMemory, SerialRequiredMemory, SerialDesiredMemory, and MaxQueryMemory.
  • RequiredMemory: Minimum amount of memory to be able to run the exectution plan at all when using the desired degree of parallelism.
  • SerialDesiredMemory: Amount of memory estimated to be needed to run the execution plan on a single node and without spilling to disk.
  • SerialRequiredMemory: Minimum amount of memory to be able to run the exectution plan at all, on a single node.
Most of these appear only in the execution plan plus run-time statistics.. For more background to some of these properties, read this post from Taiob Ali.
MissingIndexesMissingIndexesLists 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.
NonParallelPlanReasonNonParallelPlanReasonUsed when the optimizer tried to create a parallel plan but failed to create one.
Microsoft has documented most values this property can take here.
Optimization LevelStatementOptmLevelThe type of optimization used when compiling this execution plan; can be TRIVIAL or FULL.
OptimizerHardware-DependentPropertiesOptimizerHardware-DependentPropertiesVarious properties of the available hardware and system configuration that were taken into account when compiling the execution plan:
  • EstimatedAvailableDegreeOfParallelism: The maximum number of threads that can be used when running the query.
  • EstimatedAvailableMemoryGrant: The expected maximum amount of memory that can be reserved for the query.
  • EstimatedPagesCached: An estimate of how many pages are currently in cache; can be used to better estimate how much data will be read from cache instead of from disk.
  • MaxCompileMemory: The maximum memory available to the compiler when it was optimizing this execution plan.
OptimizerStatsUsageOptimizerStatsUsageLists 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 ListParameterListLists 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).
ParentObjectIdParentObjectIdCurrently unknown.
PlanGuideDBPlanGuideDBIf a plan guide was successfully applied to the query, then this property lists the database where the plan guide is defined.
PlanGuideNamePlanGuideNameIf a plan guide was successfully applied to the query, then this property lists the name of the plan guide.
QueryHashQueryHashA hash value based on the query text, which can be used to find plans for similar or equal queries.
QueryPlanHashQueryPlanHashA hash value based on the execution plan, which can be used to find plans for similar or equal execution plans.
QueryTimeStatsQueryTimeStatsDetailed information on elapsed time and CPU time used when running the execution plan.
  • CpuTime: Total CPU time used (in ms) when the plan executed. For a parallel plan, this is the total across all threads.
  • ElapsedTime: Total elapsed time (in ms) when the plan executed.
  • UdfCpuTime: Total CPU time used (in ms) within scalar user-defined functions when the plan executed. For a parallel plan, this is the total across all threads. Not shown when no scalar user-defined functions were executed.
  • UdfElapsedTime: Total elapsed time (in ms) within scalar user-defined functions when the plan executed. For a parallel plan, this is the total across all threads. Not shown when no scalar user-defined functions were executed.
Only available in execution plan plus run-time statistics.
Introduced in SQL Server 2016.
Reason For Early Termination Of Statement OptimizationStatementOptm­EarlyAbortReasonSpecifies 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.
RetrievedFromCacheRetrievedFromCacheShows 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:
  • the first execution of a query when the “optimize for ad hoc workloads” server setting is on.
  • any execution of a query with a RECOMPILE hint.
  • any time an execution plan only (“estimated execution plan”) is requested for a query that was not already in the procedure cache (so this is the only case where the property actually means what most people would intuitively think it means)
  • any execution of a query where the compiler attempted to perform simple parametrization but then rejected it as unsafe. (This last case might be due to a bug because the execution plan in this case does actually show the parametrized version of the plan that was rejected).
SecurityPolicyAppliedSecurityPolicyAppliedWhen 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 OptionsStatementSetOptionsThe 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.
StatementStatementTextThe query text of the statement that created this execution plan, or the first 4000 characters of it if the text exceeds that length.
StatementParameterizationTypeStatementParameterizationTypeThe 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.
StatementSqlHandleStatementSqlHandleA 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)StatementTypeThe 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.
ThreadStatThreadStatDetailed information about the threads used while running the execution plan. Click here more detailed information.
Not included in a serial plan.
  • Branches: The number of branches in the parallel execution plan.
    A branch is a section of the execution plan that is fully enclosed by exchange operators.
  • ThreadReservation.Node ID: Currently unknown. (Appears to be always zero)
  • ThreadReservation.ReservedThreads: The maximum number of concurrently executing threads.
    The ReservedThreads value is often equal to the number of Branches multiplied by the degree of parallelism for the execution plan. It may be lower if, due to blocking operators, there are branches that are guaranteed to only start if other branches have already ended, because in that case the threads for those branches can be reused.
  • UsedThreads: The highest number of threads that was in use at any time during execution of the query.
    This value is never more than ReservedThreads; it may be lower if any thread happened to be finished with one branch before another branch started, allowing the thread to be reused.
In an execution plan only, only the Branches sub-property is included.
TraceFlagsTraceFlagsLists 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.
Use planUsePlanThis 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.
WaitStatsWaitStatsLists 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.
WarningsWarningsLists 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.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close