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. 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.

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 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 nameXML element nameDescription
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), and 140 (SQL Server 2017).
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.
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.
Degree of ParallelismDegreeOfParallelismThe number of parallel nodes when executing the plan. Actual execution plan only.
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. Actual execution plan only.
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.

  • 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 are only in actual execution plans.
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 created one.
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 actual execution plan).
ParentObjectIdParentObjectIdCurrently unknown.
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.
QueryTimeStatsQueryTimeStatsReports the CPU time and elapsed time (in ms) used when running the execution plan. Actual execution plan only.
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 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.
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.
ThreadStatThreadStatDetailed information about the threads used while running the execution plan. Not included in a serial plan.

  • Branches: Currently unknown. (Often equal to the number of parallel sections in the execution plan, but I have seen at least one example where the number of branches was higher than the number of parallel sections)

  • ThreadReservation.Node ID: Currently unknown. (Appears to be always zero)

  • ThreadReservation.ReservedThreads: The number of reserved threads; this is equal to the product of Branches and the actual degree of parallelism used when running the plan.

  • UsedThreads: The number of threads that actually did work when running the plan.

In an estimated execution plan, 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 those that were active during execution (IsCompileTime = false).
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. Actual execution plan only.
WarningsWarningsLists all warnings that were generated during compilation of the execution plan. In an actual plan, additional run-time warning may be added.
Menu

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