question

sakuraime avatar image
0 Votes"
sakuraime asked sakuraime commented

Demo for Plan forcing support for fast forward and static cursors

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


sql-server-general
image.png (50.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered sakuraime commented

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



image.png (46.0 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

so for forced plan id 2828... it's already a CURSOR FAST_FORWARD.

which means this feature is going to force this FAST_FORWARD , to ensure it won't use STATIC??

0 Votes 0 ·

hi.. and I am still confusing that it is .

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered sakuraime commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·