Skip to content

Latest commit

 

History

History
170 lines (144 loc) · 12.5 KB

sys-query-store-plan-transact-sql.md

File metadata and controls

170 lines (144 loc) · 12.5 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.query_store_plan (Transact-SQL)
The sys.query_store_plan view contains information about each execution plan associated with a query.
rwestMSFT
randolphwest
wiassaf
02/12/2024
sql
system-objects
reference
QUERY_STORE_PLAN_TSQL
SYS.QUERY_STORE_PLAN
SYS.QUERY_STORE_PLAN_TSQL
QUERY_STORE_PLAN
query_store_plan catalog view
sys.query_store_plan catalog view
TSQL
=azuresqldb-current || >=sql-server-2016 || =azure-sqldw-latest || >=sql-server-linux-2017 || =azuresqldb-mi-current

sys.query_store_plan (Transact-SQL)

[!INCLUDE sqlserver2016-asdb-asdbmi-asa]

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 isn't 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.

Note: [!INCLUDE ssazuresynapse_md] always returns 0.
is_trivial_plan bit Plan is a trivial plan (output in stage 0 of query optimizer).

Note: [!INCLUDE ssazuresynapse_md] always returns 0.
is_parallel_plan bit Plan is parallel.

Note: [!INCLUDE ssazuresynapse_md] always returns 1.
is_forced_plan bit Plan is marked as forced when user executes stored procedure sys.sp_query_store_force_plan. The forcing mechanism doesn't guarantee that this exact 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 a similar plan to the plan referenced by plan_id. If plan forcing doesn't succeed, force_failure_count is incremented, and last_force_failure_reason is populated with the failure reason.

Note: [!INCLUDE ssazuresynapse_md] always returns 0.
is_natively_compiled bit Plan includes natively compiled memory optimized procedures. (0 = FALSE, 1 = TRUE).

Note: [!INCLUDE ssazuresynapse_md] always returns 0.
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). Resets to 0 every time is_plan_forced is changed from FALSE to TRUE.

Note: [!INCLUDE ssazuresynapse_md] always returns 0.
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
3617: COMPILATION_ABORTED_BY_CLIENT
8637: ONLINE_INDEX_BUILD
8675: OPTIMIZATION_REPLAY_FAILED
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: [!INCLUDE ssazuresynapse_md] always returns 0.
last_force_failure_reason_desc nvarchar(128) Textual description of last_force_failure_reason.

COMPILATION_ABORTED_BY_CLIENT: client aborted query compilation before it completed
ONLINE_INDEX_BUILD: query tries to modify data while target table has an index that is being built online
OPTIMIZATION_REPLAY_FAILED: The optimization replay script failed to execute.
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 doesn't exist
HINT_CONFLICT: Query can't be compiled because plan conflicts with a query hint
DQ_NO_FORCING_SUPPORTED: Can't execute query because plan conflicts with use of distributed query or full-text operations.
NO_PLAN: Query processor couldn't produce query plan, because forced plan couldn't be verified as valid for the query
NO_INDEX: Index specified in plan no longer exists
VIEW_COMPILE_FAILED: Couldn't force query plan because of a problem in an indexed view referenced in the plan
GENERAL_FAILURE: general forcing error (not covered with other reasons)

Note: [!INCLUDE ssazuresynapse_md] always returns NONE.
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, in microseconds. Divide by 1,000,000 to get seconds.
last_compile_duration bigint Plan compilation statistics, in microseconds. Divide by 1,000,000 to get seconds.
plan_forcing_type int Applies to: [!INCLUDE sssql17-md] and later versions

Plan forcing type.

0: NONE
1: MANUAL
2: AUTO
plan_forcing_type_desc nvarchar(60) Applies to: [!INCLUDE sssql17-md] and later versions

Text description of plan_forcing_type.

NONE: No plan forcing
MANUAL: Plan forced by user
AUTO: Plan forced by automatic tuning.
has_compile_replay_script bit Applies to: [!INCLUDE sql-server-2022] and later versions

Indicates whether the plan has an optimization replay script associated with it:
0 = No optimization replay script (none or even invalid).
1 = optimization replay script recorded.

Not applicable to [!INCLUDE ssazuresynapse_md].
is_optimized_plan_forcing_disabled bit Applies to: [!INCLUDE sql-server-2022] and later versions

Indicates whether optimized plan forcing was disabled for the plan:
0 = disabled.
1 = not disabled.

Not applicable to [!INCLUDE ssazuresynapse_md].
plan_type int Applies to: [!INCLUDE sql-server-2022] and later versions

Plan type.
0: Compiled Plan
1: Dispatcher Plan
2: Query Variant Plan

Not applicable to [!INCLUDE ssazuresynapse_md].
plan_type_desc nvarchar(120) Applies to: [!INCLUDE sql-server-2022] and later versions

Text description of the plan type.
Compiled Plan: Indicates that the plan is a non-parameter sensitive plan optimized plan
Dispatcher Plan: Indicates that the plan is a parameter sensitive plan optimized dispatcher plan
Query Variant Plan: Indicates that the plan is a parameter sensitive plan optimized query variant plan

Not applicable to [!INCLUDE ssazuresynapse_md].

Remarks

More than one plan can be forced when Query Store for secondary replicas is enabled.

In [!INCLUDE ssazuresynapse_md], using columns has_compile_replay_script, is_optimized_plan_forcing_disabled, plan_type, plan_type_desc results in an Invalid Column Name error as they aren't supported. See Example B for an example of how to use sys.query_store_plan in [!INCLUDE ssazuresynapse_md].

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
  • Reference to an external table
  • Distributed query or full-text operations
  • Use of elastic queries
  • Dynamic or keyset cursors
  • Invalid star join specification

Note

Azure SQL Database and SQL Server 2019 and later build versions support plan forcing for static and fast forward cursors.

Second, when objects that plan relies on, are no longer available:

  • Database (if database, where plan originated, doesn't 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.

Examples

A. Find the reason SQL Server couldn't force a plan via QDS

Pay attention to the last_force_failure_reason_desc and force_failure_count columns:

SELECT TOP 1000
    p.query_id,
    p.plan_id,
    p.last_force_failure_reason_desc,
    p.force_failure_count,
    p.last_compile_start_time,
    p.last_execution_time,
    q.last_bind_duration,
    q.query_parameterization_type_desc,
    q.context_settings_id,
    c.set_options,
    c.STATUS
FROM sys.query_store_plan p
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_context_settings c
    ON c.context_settings_id = q.context_settings_id
LEFT JOIN sys.query_store_query_text t
    ON q.query_text_id = t.query_text_id
WHERE p.is_forced_plan = 1
    AND p.last_force_failure_reason != 0;

B. Query to view query plan results in Azure Synapse Analytics

Use the following sample query to find the 100 most recent execution plans in the Query Store in [!INCLUDE ssazuresynapse_md].

SELECT TOP 100
    plan_id,
    query_id,
    plan_group_id,
    engine_version,
    compatibility_level,
    query_plan_hash,
    query_plan,
    is_online_index_plan,
    is_trivial_plan,
    is_parallel_plan,
    is_forced_plan,
    is_natively_compiled,
    force_failure_count,
    last_force_failure_reason,
    last_force_failure_reason_desc,
    count_compiles,
    initial_compile_start_time,
    last_compile_start_time,
    last_execution_time,
    avg_compile_duration,
    last_compile_duration,
    plan_forcing_type,
    plan_forcing_type_desc
FROM sys.query_store_plan
ORDER BY last_execution_time DESC;

Related content