sys.query_store_plan (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Contains information about each execution plan associated with a query.

Column name Data type Description
plan_id bigint Primary key.
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

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
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 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
  • Cursors
  • 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

Permissions

Requires the VIEW DATABASE STATE permission.

See Also

sys.database_query_store_options (Transact-SQL)
sys.query_context_settings (Transact-SQL)
sys.query_store_query (Transact-SQL)
sys.query_store_query_text (Transact-SQL)
sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)
sys.query_store_runtime_stats_interval (Transact-SQL)
Monitoring Performance By Using the Query Store
Catalog Views (Transact-SQL)
Query Store Stored Procedures (Transact-SQL)