Extract query plans from the Query Store
In this we will learn how to extract query plans from the Query Store.
This contains details about each query’s execution strategy.
plan_id
Data type: bigint
Description: Primary key.
query_id
Data type: bigint
Description: Foreign key. Joins to sys.query_store_query (Transact-SQL).
plan_group_id
Data type: bigint
Description: The plan group’s ID. Cursor queries, on the other hand, frequently need several (fill and fetch) plans. Then, in the same group, fill and retrieve plans that have been compiled together. The value 0 indicates that the plan is not part of a group.
engine_version
Data type: nvarchar(32)
Description: Version of the engine used to compile the plan in ‘major.minor.build.revision’ format.
compatibility_level
Data type: smallint
Description: Database compatibility level of the database referenced in the query.
query_plan_hash
Data type: binary(8)
Description: MD5 hash of the individual plan.
is_forced_plan
Data type: bit
Description: When the user calls the stored procedure sys.sp_query_store_force_plan, the plan is tagged as forced. However, the forcing technique does not ensure that this plan would be utilised for query_id. Plan forcing forces the query to be recompiled, which usually results in a plan that is identical to or similar to the plan indicated by plan_id. If plan forcing fails, force_failure count is increased, and last_force_failure_reason is filled with the reason for the failure.
last_force_failure_reason
Data type: int
Description: Reason why plan forcing failed.
- Firstly, 0: no failure, otherwise error number of the error that caused the forcing to fail
- Secondly, 8637: ONLINE_INDEX_BUILD
- Then, 8683: INVALID_STARJOIN
- 8684: TIME_OUT
- After that, 8689: NO_DB
- 8690: HINT_CONFLICT
- 8691: SETOPT_CONFLICT
- Next, 8694: DQ_NO_FORCING_SUPPORTED
- 8698: NO_PLAN
- 8712: NO_INDEX
- Then, 8713: VIEW_COMPILE_FAILED
- : GENERAL_FAILURE
last_force_failure_reason_desc
Data type: nvarchar(128)
Description: 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)
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:
- Firstly, insert bulk statement.
- Secondly, reference to an external table
- Thirdly, distributed query or full-text operations
- Then, use of Global queries
- After that, dynamic or keyset cursors
- Lastly, invalid star join specification
Second, when objects that plan relies on, are no longer available:
- Firstly, Database (if Database, where plan originated, does not exist anymore)
- Secondly, Index (no longer there or disabled)
Finally, problems with the plan itself:
- Firstly, not legal for query
- Secondly, Query Optimizer exceeded number of allowed operations
- Lastly, incorrectly formed plan XML
Reference: Microsoft Documentation