Demo for Plan forcing support for fast forward and static cursors

sakuraime 2,316 Reputation points
2021-07-10T14:34:49.253+00:00

Anyone has a demo script / test for the following feature in SQL Server 2019 ?
113563-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,622 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-07-12T07:06:32.42+00:00

    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.


  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-07-15T06:51:24.157+00:00

    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.

    114935-image.png

    Best Regards,
    Amelia