Anyone has a demo script / test for the following feature in SQL Server 2019 ?
Anyone has a demo script / test for the following feature in SQL Server 2019 ?
Hi sakuraime,
To enable forcing query execution plans, please use:
sp_query_store_force_plan [ @query_id = ] query_id , [ @plan_id = ] plan_id [;]
query_id Is the id of the query. plan_id Is the id of the query plan to be forced
We can use the query to return 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
JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
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 EXEC sp_query_store_force_plan [ @query_id = ] query_id , [ @plan_id = ] plan_id [;] to force the query to use a plan.
Best Regards,
Amelia
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
I get a bit confused . sp_query_store_force_plan already there is sql server before 2019 .
so these feature is talking about force plan specifically for cursor ...... and that's the example I am looking for , but not only the procedure sp_query_store_force_plan
Hi sakuraime,
Thanks for your reply.
sp_query_store_force_plan already there is sql server before 2019.
After doing some tests, it seems I can use sp_query_store_force_plan to force query execution plans for fast forward and static cursors in SQL Server 2017. This seems to be different from what the documentation mentioned.

Best Regards,
Amelia
14 people are following this question.