Skip to content

Monitoring performance by using the Query Store

    The SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. You can configure query store using the ALTER DATABASE SET option.

    If you are using Query Store for just in time workload insights in SQL Server 2016 (13.x), plan to install the performance scalability fixes in KB 4340759 as soon as possible.

    Enabling the Query Store

    Query Store is not enabled by default for new SQL Server and Azure Synapse Analytics (SQL DW) databases, and is enabled by default for new Azure SQL Database databases.

    Use the Query Store Page in SQL Server Management Studio

    1. In Object Explorer, right-click a database, and then click Properties.
    2. In the Database Properties dialog box, select the Query Store page.
    3. In the Operation Mode (Requested) box, select Read Write.

    Use Transact-SQL Statements

    Use the ALTER DATABASE statement to enable the query store for a given database. For example:

    SQL

    SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

    For more syntax options related to the Query Store, see ALTER DATABASE SET Options (Transact-SQL).

    Information in the Query Store

    Execution plans for any specific query in SQL Server typically evolve over time due to a number of different reasons such as statistics changes, schema changes, creation/deletion of indexes, etc. The procedure cache (where cached query plans are stored) only stores the latest execution plan. Plans also get evicted from the plan cache due to memory pressure. As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

    Since the Query Store retains multiple execution plans per query, it can enforce policies to direct the Query Processor to use a specific execution plan for a query. This is referred to as plan forcing. Plan forcing in Query Store is provided by using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

    Query Store does not collect data for natively compiled stored procedures by default. Use sys.sp_xtp_control_query_exec_stats to enable data collection for natively compiled stored procedures.

    Wait stats are another source of information that helps to troubleshoot performance in the Database Engine. For a long time, wait statistics were available only on instance level, which made it hard to backtrack waits to a specific query. Starting with SQL Server 2017 (14.x) and Azure SQL Database, Query Store includes a dimension that tracks wait stats. The following example enables the Query Store to collect wait stats.

    SQL

    SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

    Common scenarios for using the Query Store feature are:

    • Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.
    • Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
    • Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
    • Audit the history of query plans for a given query.
    • Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.
    • Identify top n queries that are waiting on resources.
    • Understand wait nature for a particular query or plan.

    The Query Store contains three stores:

    • plan store for persisting the execution plan information.
    • runtime stats store for persisting the execution statistics information.
    • wait stats store for persisting wait statistics information.

    The number of unique plans that can be stored for a query in the plan store is limited by the max_plans_per_query configuration option. To enhance performance, the information is written to the stores asynchronously. To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. The information in these stores is visible by querying the Query Store catalog views.

    The following query returns information about queries and plans in the Query Store.

    SQL

    SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*

    FROM sys.query_store_plan AS Pl

    INNER JOIN sys.query_store_query AS Qry

        ON Pl.query_id = Qry.query_id

    INNER JOIN sys.query_store_query_text AS Txt

        ON Qry.query_text_id = Txt.query_text_id ;

    Use the Regressed Queries feature

    After enabling the Query Store, refresh the database portion of the Object Explorer pane to add the Query Store section.

    Select Regressed Queries to open the Regressed Queries pane in SQL Server Management Studio. The Regressed Queries pane shows you the queries and plans in the query store. Use the drop-down boxes at the top to filter queries based on various criteria: Duration (ms) (Default), CPU Time (ms), Logical Reads (KB), Logical Writes (KB), Physical Reads (KB), CLR Time (ms), DOP, Memory Consumption (KB), Row Count, Log Memory Used (KB), Temp DB Memory Used (KB), and Wait Time (ms).

    Select a plan to see the graphical query plan. Buttons are available to view the source query, force and unforce a query plan, toggle between grid and chart formats, compare selected plans (if more than one is selected), and refresh the display.

    To force a plan, select a query and plan, and then click Force Plan. You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

    Finding waiting queries

    Starting with SQL Server 2017 (14.x) and Azure SQL Database, wait statistics per query over time are available in Query Store.

    In Query Store, wait types are combined into wait categories. The mapping of wait categories to wait types is available in sys.query_store_wait_stats (Transact-SQL).

    Select Query Wait Statistics to open the Query Wait Statistics pane in SQL Server Management Studio v18 or higher. The Query Wait Statistics pane shows you a bar chart containing the top wait categories in the Query Store. Use the drop-down at the top to select an aggregate criteria for the wait time: avg, max, min, std dev, and total (default).

    Select a wait category by clicking on the bar and a detail view on the selected wait category displays. This new bar chart contains the queries that contributed to that wait category.

    Use the drop-down box at the top to filter queries based on various wait time criteria for the selected wait category: avg, max, min, std dev, and total (default). Select a plan to see the graphical query plan. Buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

    Wait categories are combining different wait types into buckets similar by nature. Different wait categories require a different follow up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete the majority of such investigations successfully.

    Here are some examples how you can get more insights into your workload before and after introducing wait categories in Query Store:

    Previous experienceNew experienceAction
    High RESOURCE_SEMAPHORE waits per databaseHigh Memory waits in Query Store for specific queriesFind the top memory consuming queries in Query Store. These queries are probably delaying further progress of the affected queries. Consider using MAX_GRANT_PERCENT query hint for these queries, or for the affected queries.
    High LCK_M_X waits per databaseHigh Lock waits in Query Store for specific queriesCheck the query texts for the affected queries and identify the target entities. Look in Query Store for other queries modifying the same entity, which are executed frequently and/or have high duration. After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
    High PAGEIOLATCH_SH waits per databaseHigh Buffer IO waits in Query Store for specific queriesFind the queries with a high number of physical reads in Query Store. If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans, and thus minimize the IO overhead of the queries.
    High SOS_SCHEDULER_YIELD waits per databaseHigh CPU waits in Query Store for specific queriesFind the top CPU consuming queries in Query Store. Among them, identify the queries for which high CPU trend correlates with high CPU waits for the affected queries. Focus on optimizing those queries – there could be a plan regression, or perhaps a missing index.

    Configuration Options

    Query the sys.database_query_store_options view to determine the current options of the Query Store.

    Related Views, Functions, and Procedures

    View and manage Query Store through Management Studio or by using the following views and procedures.

    Query Store Functions

    Functions help operations with the Query Store.

    sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

    Query Store Catalog Views

    Catalog views present information about the Query Store.

    sys.database_query_store_options (Transact-SQL)

    sys.query_context_settings (Transact-SQL)

    sys.query_store_plan (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)

    Query Store Stored Procedures

    Stored procedures configure the Query Store.

    sp_query_store_flush_db (Transact-SQL)

    sp_query_store_reset_exec_stats (Transact-SQL)

    sp_query_store_force_plan (Transact-SQL)

    sp_query_store_unforce_plan (Transact-SQL)

    sp_query_store_remove_plan (Transact-SQL)

    sp_query_store_remove_query (Transact-SQL)

    sp_query_store_consistency_check (Transact-SQL)1

    In extreme scenarios Query Store can enter an ERROR state because of internal errors. Starting with SQL Server 2017 (14.x), if this happens, Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure in the affected database. See sys.database_query_store_options for more details described in the actual_state_desc column description.

    This section provides some guidelines on managing Query Store feature itself.

    Is Query Store currently active?

    Query Store stores its data inside the user database and that is why it has size limit (configured with MAX_STORAGE_SIZE_MB). If data in Query Store hits that limit Query Store will automatically change state from read-write to read-only and stop collecting new data.

    Query sys.database_query_store_options to determine if Query Store is currently active, and whether it is currently collects runtime stats or not.

    SQL

    SELECT actual_state, actual_state_desc, readonly_reason,

        current_storage_size_mb, max_storage_size_mb

    FROM sys.database_query_store_options;

    Query Store status is determined by actual_state column. If it’s different than the desired status, the readonly_reason column can give you more information. When Query Store size exceeds the quota, the feature will switch to read_only mode.

    Get Query Store options

    To find out detailed information about Query Store status, execute following in a user database.

    SQL

    SELECT * FROM sys.database_query_store_options;

    Setting Query Store interval

    You can override interval for aggregating query runtime statistics (default is 60 minutes).

    SQL

    ALTER DATABASE <database_name>

    SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

    New value for interval is exposed through sys.database_query_store_options view.

    Query Store space usage

    To check current the Query Store size and limit execute the following statement in the user database.

    SQL

    SELECT current_storage_size_mb, max_storage_size_mb

    FROM sys.database_query_store_options;

    If the Query Store storage is full use the following statement to extend the storage.

    SQL

    ALTER DATABASE <database_name>

    SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

    Set Query Store options

    You can set multiple Query Store options at once with a single ALTER DATABASE statement.

    SQL

    ALTER DATABASE <database name>

    SET QUERY_STORE (

        OPERATION_MODE = READ_WRITE,

        CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),

        DATA_FLUSH_INTERVAL_SECONDS = 3000,

        MAX_STORAGE_SIZE_MB = 500,

        INTERVAL_LENGTH_MINUTES = 15,

        SIZE_BASED_CLEANUP_MODE = AUTO,

        QUERY_CAPTURE_MODE = AUTO,

        MAX_PLANS_PER_QUERY = 1000,

        WAIT_STATS_CAPTURE_MODE = ON

    );

    Cleaning up the space

    Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. If you are running out of space you might want to clear older Query Store data by using the following statement.

    SQL

    ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

    Alternatively, you might want to clear up only ad-hoc query data, since it is less relevant for query optimizations and plan analysis but takes up just as much space.

    Delete ad-hoc queries

    This purges adhoc and internal queries from the query store every 3 minutes so that the query store does not run out of space and remove queries we really need to track

    SQL

    SET NOCOUNT ON

    — This purges adhoc and internal queries from the query store every 3 minutes so that the

    — query store does not run out of space and remove queries we really need to track

    DECLARE @command varchar(1000)

    SELECT @command = ‘IF ”?” NOT IN(”master”, ”model”, ”msdb”, ”tempdb”) BEGIN USE ?

    EXEC(”

    DECLARE @id int

    DECLARE adhoc_queries_cursor CURSOR

    FOR

    SELECT q.query_id

    FROM sys.query_store_query_text AS qt

    JOIN sys.query_store_query AS q

    ON q.query_text_id = qt.query_text_id

    JOIN sys.query_store_plan AS p

    ON p.query_id = q.query_id

    JOIN sys.query_store_runtime_stats AS rs

    ON rs.plan_id = p.plan_id

    WHERE q.is_internal_query = 1 ‘ — is it an internal query then we dont care to keep track of it

    ‘ OR q.object_id = 0’ — if it does not have a valid object_id then it is an adhoc query and we dont care about keeping track of it

    ‘ GROUP BY q.query_id

    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE()) ‘ — if it has been more than 5 minutes since the adhoc query ran

    ‘ ORDER BY q.query_id ;

    OPEN adhoc_queries_cursor ;

    FETCH NEXT FROM adhoc_queries_cursor INTO @id;

    WHILE @@fetch_status = 0

    BEGIN

    EXEC sp_query_store_remove_query @id

    FETCH NEXT FROM adhoc_queries_cursor INTO @id

    END

    CLOSE adhoc_queries_cursor ;

    DEALLOCATE adhoc_queries_cursor;

    ”) END’ ;

    EXEC sp_MSforeachdb @command

    You can define your own procedure with different logic for clearing up data you no longer want.

    The example above uses the sp_query_store_remove_query extended stored procedure for removing unnecessary data. You can also use:

    • sp_query_store_reset_exec_stats to clear runtime statistics for a given plan.
    • sp_query_store_remove_plan to remove a single plan.

    Performance Auditing and Troubleshooting

    Query Store keeps a history of compilation and runtime metrics throughout query executions, allowing you to ask questions about your workload.

    Last n queries executed on the database?

    SQL

    SELECT TOP 10 qt.query_sql_text, q.query_id,

        qt.query_text_id, p.plan_id, rs.last_execution_time

    FROM sys.query_store_query_text AS qt

    JOIN sys.query_store_query AS q

        ON qt.query_text_id = q.query_text_id

    JOIN sys.query_store_plan AS p

        ON q.query_id = p.query_id

    JOIN sys.query_store_runtime_stats AS rs

        ON p.plan_id = rs.plan_id

    ORDER BY rs.last_execution_time DESC;

    Number of executions for each query?

    SQL

    SELECT q.query_id, qt.query_text_id, qt.query_sql_text,

        SUM(rs.count_executions) AS total_execution_count

    FROM sys.query_store_query_text AS qt

    JOIN sys.query_store_query AS q

        ON qt.query_text_id = q.query_text_id

    JOIN sys.query_store_plan AS p

        ON q.query_id = p.query_id

    JOIN sys.query_store_runtime_stats AS rs

        ON p.plan_id = rs.plan_id

    GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text

    ORDER BY total_execution_count DESC;

    The number of queries with the longest average execution time within last hour?

    SQL

    SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,

        qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,

        rs.last_execution_time

    FROM sys.query_store_query_text AS qt

    JOIN sys.query_store_query AS q

        ON qt.query_text_id = q.query_text_id

    JOIN sys.query_store_plan AS p

        ON q.query_id = p.query_id

    JOIN sys.query_store_runtime_stats AS rs

        ON p.plan_id = rs.plan_id

    WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())

    ORDER BY rs.avg_duration DESC;

    The number of queries that had the biggest average physical I/O reads in last 24 hours, with corresponding average row count and execution count?

    SQL

    SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,

        q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,

        rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions

    FROM sys.query_store_query_text AS qt

    JOIN sys.query_store_query AS q

        ON qt.query_text_id = q.query_text_id

    JOIN sys.query_store_plan AS p

        ON q.query_id = p.query_id

    JOIN sys.query_store_runtime_stats AS rs

        ON p.plan_id = rs.plan_id

    JOIN sys.query_store_runtime_stats_interval AS rsi

        ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id

    WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())

    ORDER BY rs.avg_physical_io_reads DESC;

    Queries with multiple plans? These queries are especially interesting because they are candidates for regressions due to plan choice change. The following query identifies these queries along with all plans:

    SQL

    WITH Query_MultPlans

    AS

    (

    SELECT COUNT(*) AS cnt, q.query_id

    FROM sys.query_store_query_text AS qt

    JOIN sys.query_store_query AS q

        ON qt.query_text_id = q.query_text_id

    JOIN sys.query_store_plan AS p

        ON p.query_id = q.query_id

    GROUP BY q.query_id

    HAVING COUNT(distinct plan_id) > 1

    )

    SELECT q.query_id, object_name(object_id) AS ContainingObject,

        query_sql_text, plan_id, p.query_plan AS plan_xml,

        p.last_compile_start_time, p.last_execution_time

    FROM Query_MultPlans AS qm

    JOIN sys.query_store_query AS q

        ON qm.query_id = q.query_id

    JOIN sys.query_store_plan AS p

        ON q.query_id = p.query_id

    JOIN sys.query_store_query_text qt

        ON qt.query_text_id = q.query_text_id

    ORDER BY query_id, plan_id;

    Queries that recently regressed in performance (comparing different point in time)? The following query example returns all queries for which execution time doubled in last 48 hours due to a plan choice change. Query compares all runtime stat intervals side by side.

    SQL

    SELECT

        qt.query_sql_text,

        q.query_id,

        qt.query_text_id,

        rs1.runtime_stats_id AS runtime_stats_id_1,

        rsi1.start_time AS interval_1,

        p1.plan_id AS plan_1,

        rs1.avg_duration AS avg_duration_1,

        rs2.avg_duration AS avg_duration_2,

        p2.plan_id AS plan_2,

        rsi2.start_time AS interval_2,

        rs2.runtime_stats_id AS runtime_stats_id_2

    FROM sys.query_store_query_text AS qt

    JOIN sys.query_store_query AS q

        ON qt.query_text_id = q.query_text_id

    JOIN sys.query_store_plan AS p1

        ON q.query_id = p1.query_id

    JOIN sys.query_store_runtime_stats AS rs1

        ON p1.plan_id = rs1.plan_id

    JOIN sys.query_store_runtime_stats_interval AS rsi1

        ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id

    JOIN sys.query_store_plan AS p2

        ON q.query_id = p2.query_id

    JOIN sys.query_store_runtime_stats AS rs2

        ON p2.plan_id = rs2.plan_id

    JOIN sys.query_store_runtime_stats_interval AS rsi2

        ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id

    WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())

        AND rsi2.start_time > rsi1.start_time

        AND p1.plan_id <> p2.plan_id

        AND rs2.avg_duration > 2*rs1.avg_duration

    ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;

    If you want to see performance all regressions (not only those related to plan choice change) than just remove condition AND p1.plan_id <> p2.plan_id from the previous query.

    Queries that are waiting the most? This query will return top 10 queries that wait the most.

    SQL

    SELECT TOP 10

        qt.query_text_id,

        q.query_id,

        p.plan_id,

        sum(total_query_wait_time_ms) AS sum_total_wait_ms

    FROM sys.query_store_wait_stats ws

    JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id

    JOIN sys.query_store_query q ON p.query_id = q.query_id

    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id

    GROUP BY qt.query_text_id, q.query_id, p.plan_id

    ORDER BY sum_total_wait_ms DESC

    Queries that recently regressed in performance (comparing recent vs. history execution)? The next query compares query execution based periods of execution. In this particular example the query compares execution in recent period (1 hour) vs. history period (last day) and identifies those that introduced additional_duration_workload. This metrics is calculated as a difference between recent average execution and history average execution multiplied by the number of recent executions. It actually represents how much of additional duration recent executions introduced compared to history:

    SQL

    — “Recent” workload – last 1 hour

    DECLARE @recent_start_time datetimeoffset;

    DECLARE @recent_end_time datetimeoffset;

    SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());

    SET @recent_end_time = SYSUTCDATETIME();

    — “History” workload

    DECLARE @history_start_time datetimeoffset;

    DECLARE @history_end_time datetimeoffset;

    SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());

    SET @history_end_time = SYSUTCDATETIME();

    WITH

    hist AS

    (

        SELECT

            p.query_id query_id,

            ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,

            SUM(rs.count_executions) AS count_executions,

            COUNT(distinct p.plan_id) AS num_plans

         FROM sys.query_store_runtime_stats AS rs

            JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id

        WHERE (rs.first_execution_time >= @history_start_time

                   AND rs.last_execution_time < @history_end_time)

            OR (rs.first_execution_time <= @history_start_time

                   AND rs.last_execution_time > @history_start_time)

            OR (rs.first_execution_time <= @history_end_time

                   AND rs.last_execution_time > @history_end_time)

        GROUP BY p.query_id

    ),

    recent AS

    (

        SELECT

            p.query_id query_id,

            ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,

            SUM(rs.count_executions) AS count_executions,

            COUNT(distinct p.plan_id) AS num_plans

        FROM sys.query_store_runtime_stats AS rs

            JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id

        WHERE  (rs.first_execution_time >= @recent_start_time

                   AND rs.last_execution_time < @recent_end_time)

            OR (rs.first_execution_time <= @recent_start_time

                   AND rs.last_execution_time > @recent_start_time)

            OR (rs.first_execution_time <= @recent_end_time

                   AND rs.last_execution_time > @recent_end_time)

        GROUP BY p.query_id

    )

    SELECT

        results.query_id AS query_id,

        results.query_text AS query_text,

        results.additional_duration_workload AS additional_duration_workload,

        results.total_duration_recent AS total_duration_recent,

        results.total_duration_hist AS total_duration_hist,

        ISNULL(results.count_executions_recent, 0) AS count_executions_recent,

        ISNULL(results.count_executions_hist, 0) AS count_executions_hist

    FROM

    (

        SELECT

            hist.query_id AS query_id,

            qt.query_sql_text AS query_text,

            ROUND(CONVERT(float, recent.total_duration/

                       recent.count_executions-hist.total_duration/hist.count_executions)

                   *(recent.count_executions), 2) AS additional_duration_workload,

            ROUND(recent.total_duration, 2) AS total_duration_recent,

            ROUND(hist.total_duration, 2) AS total_duration_hist,

            recent.count_executions AS count_executions_recent,

            hist.count_executions AS count_executions_hist

        FROM hist

            JOIN recent

                ON hist.query_id = recent.query_id

            JOIN sys.query_store_query AS q

                ON q.query_id = hist.query_id

            JOIN sys.query_store_query_text AS qt

                ON q.query_text_id = qt.query_text_id

    ) AS results

    WHERE additional_duration_workload > 0

    ORDER BY additional_duration_workload DESC

    OPTION (MERGE JOIN);

    Maintaining query performance stability

    For queries executed multiple times you may notice that SQL Server uses different plans, resulting in different resource utilization and duration. With Query Store you can detect when query performance regressed and determine the optimal plan within a period of interest. You can then force that optimal plan for future query execution.

    You can also identify inconsistent query performance for a query with parameters (either auto-parameterized or manually parameterized). Among different plans you can identify the plan which is fast and optimal enough for all or most of the parameter values and force that plan, keeping predictable performance for the wider set of user scenarios.

    Force a plan for a query (apply forcing policy)

    When a plan is forced for a certain query, SQL Server tries to force the plan in the optimizer. If plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

    SQL

    EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

    When using sp_query_store_force_plan you can only force plans that were recorded by Query Store as a plan for that query. In other words, the only plans available for a query are those that were already used to execute that query while Query Store was active.

    Plan forcing support for fast forward and static cursors

    Starting with SQL Server 2019 (15.x) and Azure SQL Database (all deployment models), Query Store supports the ability to force query execution plans for fast forward and static Transact-SQL and API cursors. Forcing is supported via sp_query_store_force_plan or through SQL Server Management Studio Query Store reports.

    Remove plan forcing for a query

    To rely again on the SQL Server query optimizer to calculate the optimal query plan, use sp_query_store_unforce_plan to unforce the plan that was selected for the query.

    SQL

    EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;