Query Store is a feature in SQL Server that allows users to monitor and troubleshoot performance issues in their databases. It provides a central repository for query plans, execution statistics, and runtime metrics, allowing users to easily identify and fix performance bottlenecks. One of the key features of Query Store is the ability to "force" a specific query plan, which can be useful in cases where the optimizer is not choosing the most efficient plan for a given query. In this blog post, we will discuss how to use Query Store plan forcing, and provide an example of how it works.
To use Query Store plan forcing, you first need to enable Query Store for the database. This can be done using the following T-SQL command:
USE [master]
GO
ALTER DATABASE [MyDatabase] SET QUERY_STORE = ON
GO
ALTER DATABASE [MyDatabase] SET QUERY_STORE (OPERATION_MODE = READ_WRITE,
DATA_FLUSH_INTERVAL_SECONDS = 3000, INTERVAL_LENGTH_MINUTES = 10)
GO
Once Query Store is enabled, you can use it to monitor the performance of your queries. This can be done using the Query Store UI in SQL Server Management Studio, or by querying the sys.query_store_query and sys.query_store_plan views.
For example, the following query returns the top 10 longest running queries in the database:
SELECT TOP 10
query_id,
duration_ms,
execution_count,
last_execution_time
FROM sys.query_store_query
ORDER BY duration_ms DESC;
To force a specific query plan, you can use the ALTER PLAN T-SQL command. This command takes two arguments: the query_id of the query, and the plan_id of the desired plan. The query_id and plan_id can be obtained from the sys.query_store_query and sys.query_store_plan views, respectively.
Here is an example of using the ALTER PLAN command to force a specific query plan:
ALTER PLAN FOR QUERY 1 WITH PLAN 3;
In this example, we are forcing the query with query_id 1 to use the plan with plan_id 3. This will cause SQL Server to always use plan 3 for this query, regardless of what the optimizer chooses.
Query Store Plan Forcing Example
In the above image, we can see an example of Query Store plan forcing in action. The top panel shows the query details, including the query_id and the different plans that have been used for the query. The bottom panel shows the plan details, including the plan_id and the actual query plan. We can see that the query is currently using plan 3, which was forced using the ALTER PLAN command.
In summary, Query Store plan forcing is a useful feature that allows users to specify a specific query plan for a given query. This can be useful in cases where the optimizer is not choosing the most efficient plan and can help to improve the performance of the database. By using the ALTER PLAN command, users can easily force a specific query plan and ensure that their queries are running efficiently.