Class QueryStoreSchema.QueryStorePlan
- Namespace
- LinqToDB.Tools.DataProvider.SqlServer.Schemas
- Assembly
- linq2db.Tools.dll
sys.query_store_plan (Transact-SQL)
Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics
Contains information about each execution plan associated with a query.
See sys.query_store_plan.
[Table(Schema = "sys", Name = "query_store_plan", IsView = true)]
public class QueryStoreSchema.QueryStorePlan- Inheritance
-
QueryStoreSchema.QueryStorePlan
- Extension Methods
Properties
AvgCompileDuration
Plan compilation statistics.
[Column("avg_compile_duration")]
[Nullable]
public double? AvgCompileDuration { get; set; }Property Value
CompatibilityLevel
Database compatibility level of the database referenced in the query.
[Column("compatibility_level")]
[NotNull]
public short CompatibilityLevel { get; set; }Property Value
CountCompiles
Plan compilation statistics.
[Column("count_compiles")]
[Nullable]
public long? CountCompiles { get; set; }Property Value
- long?
EngineVersion
Version of the engine used to compile the plan in 'major.minor.build.revision' format.
[Column("engine_version")]
[Nullable]
public string? EngineVersion { get; set; }Property Value
ForceFailureCount
Number of times that forcing this plan has failed. It can be incremented only when the query is recompiled (not on every execution). It is reset to 0 every time is_plan_forced is changed from FALSE to TRUE.
Note: Azure Synapse Analytics will always return zero (0).
[Column("force_failure_count")]
[NotNull]
public long ForceFailureCount { get; set; }Property Value
InitialCompileStartTime
Plan compilation statistics.
[Column("initial_compile_start_time")]
[NotNull]
public DateTimeOffset InitialCompileStartTime { get; set; }Property Value
IsForcedPlan
Plan is marked as forced when user executes stored procedure sys.sp_query_store_force_plan. Forcing mechanism does not guarantee that exactly this plan will be used for the query referenced by query_id. Plan forcing causes query to be compiled again and typically produces exactly the same or similar plan to the plan referenced by plan_id. If plan forcing does not succeed, force_failure_count is incremented and last_force_failure_reason is populated with the failure reason.
Note: Azure Synapse Analytics will always return zero (0).
[Column("is_forced_plan")]
[NotNull]
public bool IsForcedPlan { get; set; }Property Value
IsNativelyCompiled
Plan includes natively compiled memory optimized procedures. (0 = FALSE, 1 = TRUE).
Note: Azure Synapse Analytics will always return zero (0).
[Column("is_natively_compiled")]
[NotNull]
public bool IsNativelyCompiled { get; set; }Property Value
IsOnlineIndexPlan
Plan was used during an online index build.
Note: Azure Synapse Analytics will always return zero (0).
[Column("is_online_index_plan")]
[NotNull]
public bool IsOnlineIndexPlan { get; set; }Property Value
IsParallelPlan
Plan is parallel.
Note: Azure Synapse Analytics will always return one (1).
[Column("is_parallel_plan")]
[NotNull]
public bool IsParallelPlan { get; set; }Property Value
IsTrivialPlan
Plan is a trivial plan (output in stage 0 of query optimizer).
Note: Azure Synapse Analytics will always return zero (0).
[Column("is_trivial_plan")]
[NotNull]
public bool IsTrivialPlan { get; set; }Property Value
LastCompileDuration
Plan compilation statistics.
[Column("last_compile_duration")]
[Nullable]
public long? LastCompileDuration { get; set; }Property Value
- long?
LastCompileStartTime
Plan compilation statistics.
[Column("last_compile_start_time")]
[Nullable]
public DateTimeOffset? LastCompileStartTime { get; set; }Property Value
LastExecutionTime
Last execution time refers to the last end time of the query/plan.
[Column("last_execution_time")]
[Nullable]
public DateTimeOffset? LastExecutionTime { get; set; }Property Value
LastForceFailureReason
Reason why plan forcing failed.
0: no failure, otherwise error number of the error that caused the forcing to fail
8637: ONLINE_INDEX_BUILD
8683: INVALID_STARJOIN
8684: TIME_OUT
8689: NO_DB
8690: HINT_CONFLICT
8691: SETOPT_CONFLICT
8694: DQ_NO_FORCING_SUPPORTED
8698: NO_PLAN
8712: NO_INDEX
8713: VIEW_COMPILE_FAILED
<other value>: GENERAL_FAILURE
Note: Azure Synapse Analytics will always return zero (0).
[Column("last_force_failure_reason")]
[NotNull]
public int LastForceFailureReason { get; set; }Property Value
LastForceFailureReasonDesc
Textual description of last_force_failure_reason_desc.
ONLINE_INDEX_BUILD: query tries to modify data while target table has an index that is being built online
INVALID_STARJOIN: plan contains invalid StarJoin specification
TIME_OUT: Optimizer exceeded number of allowed operations while searching for plan specified by forced plan
NO_DB: A database specified in the plan does not exist
HINT_CONFLICT: Query cannot be compiled because plan conflicts with a query hint
DQ_NO_FORCING_SUPPORTED: Cannot execute query because plan conflicts with use of distributed query or full-text operations.
NO_PLAN: Query processor could not produce query plan because forced plan could not be verified to be valid for the query
NO_INDEX: Index specified in plan no longer exists
VIEW_COMPILE_FAILED: Could not force query plan because of a problem in an indexed view referenced in the plan
GENERAL_FAILURE: general forcing error (not covered with reasons above)
Note: Azure Synapse Analytics will always return NONE.
[Column("last_force_failure_reason_desc")]
[Nullable]
public string? LastForceFailureReasonDesc { get; set; }Property Value
PlanForcingType
Plan forcing type.
0: NONE
1: MANUAL
2: AUTO
[Column("plan_forcing_type")]
[NotNull]
public int PlanForcingType { get; set; }Property Value
PlanForcingTypeDesc
Text description of plan_forcing_type.
NONE: No plan forcing
MANUAL: Plan forced by user
AUTO: Plan forced by automatic tuning
[Column("plan_forcing_type_desc")]
[Nullable]
public string? PlanForcingTypeDesc { get; set; }Property Value
PlanGroupID
ID of the plan group. Cursor queries typically require multiple (populate and fetch) plans. Populate and fetch plans that are compiled together are in the same group.
0 means plan is not in a group.
[Column("plan_group_id")]
[Nullable]
public long? PlanGroupID { get; set; }Property Value
- long?
PlanID
Primary key.
[Column("plan_id")]
[NotNull]
public long PlanID { get; set; }Property Value
QueryID
Foreign key. Joins to sys.query_store_query (Transact-SQL).
[Column("query_id")]
[NotNull]
public long QueryID { get; set; }Property Value
QueryPlan
Showplan XML for the query plan.
[Column("query_plan")]
[Nullable]
public string? QueryPlan { get; set; }Property Value
QueryPlanHash
MD5 hash of the individual plan.
[Column("query_plan_hash")]
[NotNull]
public byte[] QueryPlanHash { get; set; }Property Value
- byte[]