Contains information about each execution plan associated with a query.
|Column name|Data type|Description|
|query_id|bigint|Foreign key. Joins to sys.query_store_query (Transact-SQL).|
|plan_group_id|bigint|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.|
|engine_version|nvarchar(32)|Version of the engine used to compile the plan in 'major.minor.build.revision' format.|
|compatibility_level|smallint|Database compatibility level of the database referenced in the query.|
|query_plan_hash|binary(8)|MD5 hash of the individual plan.|
|query_plan|nvarchar(max)|Showplan XML for the query plan.|
|is_online_index_plan|bit|Plan was used during an online index build.|
|is_trivial_plan|bit|Plan is a trivial plan (output in stage 0 of query optimizer).|
|is_parallel_plan|bit|Plan is parallel.|
|is_forced_plan|bit|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.|
|is_natively_compiled|bit|Plan includes natively compiled memory optimized procedures. (0 = FALSE, 1 = TRUE).|
|force_failure_count|bigint|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.|
|last_force_failure_reason|int|Reason why plan forcing failed.
0: no failure, otherwise error number of the error that caused the forcing to fail
<other value>: GENERAL_FAILURE|
|last_force_failure_reason_desc|nvarchar(128)|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)|
|count_compiles|bigint|Plan compilation statistics.|
|initial_compile_start_time|datetimeoffset|Plan compilation statistics.|
|last_compile_start_time|datetimeoffset|Plan compilation statistics.|
|last_execution_time|datetimeoffset|Last execution time refers to the last end time of the query/plan.|
|avg_compile_duration|float|Plan compilation statistics.|
|last_compile_duration|bigint|Plan compilation statistics.|
|plan_forcing_type|int|Plan forcing type.
2: AUTO| |plan_forcing_type_desc|nvarchar(60)|Text description of plan_forcing_type.
NONE: No plan forcing
MANUAL: Plan forced by user
AUTO: Plan forced by automatic tuning|
Plan forcing limitations
Query Store has a mechanism to enforce Query Optimizer to use certain execution plan. However, there are some limitations that can prevent a plan to be enforced.
First, if the plan contains following constructions:
- Insert bulk statement.
- Insert bulk statement.
- Reference to an external table
- Distributed query or full-text operations
- Use of Global queries
- Invalid star join specification
Second, when objects that plan relies on, are no longer available:
- Database (if Database, where plan originated, does not exist anymore)
- Index (no longer there or disabled)
Finally, problems with the plan itself:
- Not legal for query
- Query Optimizer exceeded number of allowed operations
- Incorrectly formed plan XML
Requires the VIEW DATABASE STATE permission.
Monitoring Performance By Using the Query Store
Catalog Views (Transact-SQL)
Query Store Stored Procedures (Transact-SQL)