title: "sys.query_store_plan (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "09/12/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database" ms.component: "system-catalog-views" ms.reviewer: "" ms.suite: "sql" ms.technology: system-objects ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:
- "QUERY_STORE_PLAN_TSQL"
- "SYS.QUERY_STORE_PLAN"
- "SYS.QUERY_STORE_PLAN_TSQL"
- "QUERY_STORE_PLAN" dev_langs:
- "TSQL" helpviewer_keywords:
- "query_store_plan catalog view"
- "sys.query_store_plan catalog view" ms.assetid: b4d05439-6360-45db-b1cd-794f4a64935e caps.latest.revision: 18 author: edmacauley ms.author: edmaca manager: craigg monikerRange: "= azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions"
[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx-md]
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. |
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
Requires the VIEW DATABASE STATE permission.
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)