Demo for Plan forcing support for fast forward and static cursors

Question

Anyone has a demo script / test for the following feature in SQL Server 2019 ?

Answer 1

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.

Answer 2

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