Skip to content

sp-query-store-unforce-plan-transact-sql.md

Latest commit

 

History

History
105 lines (78 loc) · 4.27 KB

sp-query-store-unforce-plan-transact-sql.md

File metadata and controls

105 lines (78 loc) · 4.27 KB
title sp_query_store_unforce_plan (Transact-SQL)
description Enables unforcing a previously forced plan for a particular query in the Query Store.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 09/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
ms.custom
ignite-2025
f1_keywords
SP_QUERY_STORE_UNFORCE_PLAN_TSQL
SP_QUERY_STORE_UNFORCE_PLAN
SYS.SP_QUERY_STORE_UNFORCE_PLAN
SYS.SP_QUERY_STORE_UNFORCE_PLAN_TSQL
helpviewer_keywords
sys.sp_query_store_unforce_plan
sp_query_store_unforce_plan
dev_langs
TSQL
monikerRange =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

sp_query_store_unforce_plan (Transact-SQL)

[!INCLUDE sqlserver2016-asdb-asdbmi-fabricsqldb]

Enables unforcing a previously forced plan for a particular query in the Query Store.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_query_store_unforce_plan
    [ @query_id = ] query_id ,
    [ @plan_id = ] plan_id ,
    [ @replica_group_id = ] 'replica_group_id'
[ ; ]

Arguments

[!INCLUDE extended-stored-procedures]

[ @query_id = ] query_id

The ID of the query. @query_id is bigint, with no default.

[ @plan_id = ] plan_id

The ID of the query plan that will no longer be enforced. @plan_id is bigint, with no default.

[ @replica_group_id = ] 'replica_group_id'

You can unforce plans on a secondary replica when Query Store for readable secondaries is enabled. Execute sp_query_store_force_plan and sp_query_store_unforce_plan on the primary replica. Using the @replica_group_id argument defaults to the local replica where the command is being executed, but you can specify a replica_group_id referencing the sys.query_store_plan_forcing_locations system catalog view.

Return code values

0 (success) or 1 (failure).

Permissions

Requires the ALTER permission on the database.

Remarks

Query Store for secondary replicas is supported starting in [!INCLUDE sssql25-md] and later versions, and in Azure SQL Database. For complete platform support, see Query Store for secondary replicas.

Examples

The following example returns information about the queries in the Query Store.

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;

After you identify the query_id and plan_id that you want to unforce, use the following example to unforce the plan.

EXECUTE sp_query_store_unforce_plan 3, 3;

Related content