Bewerken

Delen via

sp_query_store_force_plan (Transact-SQL)

  • Artikel

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance

Enables forcing a particular plan for a particular query in the Query Store.

When a plan is forced for a particular query, every time SQL Server encounters the query, it tries to force the plan in the Query Optimizer. If plan forcing fails, an Extended Event is fired and the Query Optimizer is instructed to optimize in the normal way.

Transact-SQL syntax conventions

Syntax

sp_query_store_force_plan
    [ @query_id = ] query_id ,
    [ @plan_id = ] plan_id ,
    [ @disable_optimized_plan_forcing = ] disable_optimized_plan_forcing ,
    [ @force_plan_scope = ] 'replica_group_id'
[ ; ]

Arguments

[ @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 to be forced. @plan_id is bigint, with no default.

[ @disable_optimized_plan_forcing = ] disable_optimized_plan_forcing

Indicates whether optimized plan forcing should be disabled. @disable_optimized_plan_forcing is bit with a default of 0.

[ @force_plan_scope = ] 'replica_group_id'

You can force plans on a secondary replica when Query Store for secondary replicas is enabled. Execute sp_query_store_force_plan and sp_query_store_unforce_plan on the secondary replica. The optional @force_plan_scope argument defaults only to the local replica (primary or secondary), but you can optionally specify a replica_group_id referencing sys.query_store_replicas.

Return code values

0 (success) or 1 (failure).

Remarks

The resulting execution plan forced by this feature is the same or similar to the plan being forced. Because the resulting plan might not be identical to the plan specified by sys.sp_query_store_force_plan, the performance of the plans might vary. In rare cases, the performance difference might be significant and negative; in that case, the administrator must remove the forced plan.

Review forced plans on secondary replicas with sys.query_store_plan_forcing_locations.

Permissions

Requires the ALTER permission on the database.

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 force, use the following example to force the query to use a plan.

EXEC sp_query_store_force_plan
    @query_id = 3,
    @plan_id = 3;

Use sys.query_store_plan_forcing_locations, joined with sys.query_store_replicas, to retrieve Query Store for secondary replicas.

SELECT query_plan
FROM sys.query_store_plan AS qsp
INNER JOIN sys.query_store_plan_forcing_locations AS pfl
    ON pfl.query_id = qsp.query_id
INNER JOIN sys.query_store_replicas AS qsr
    ON qsr.replica_group_id = qsp.replica_group_id
WHERE qsr.replica_name = 'yourSecondaryReplicaName';

Related content