I have enabled Query Store on a database. I have a specific query which I want to track. I have a lot of details about the query from sp_BlitzCache (like SQL Text, SQL Handle, SQL Hash, Plan Cache Handle/Hash, etc…).
Am I able to search Query Store with the info from sp_BlitzCache to track down the query there? I want to force a specific execution plan because the query suffers parameter sniffing issues.
Best Answer
One way to do this is to query the Query Store views directly for the info you've gained from the plan cache:
The
sys.query_store_query
table also has some of the other fields (query_hash
,last_compile_batch_sql_handle
,statement_sql_handle
, etc), which might find the query you're looking for more reliably.Basically the point of that is to find the "query id" in Query Store, as you'll need that to find plans for it and force one.
Once you have the
query_id
in hand, you can go to the UI in SSMS and find the query using the "Tracked Queries" node:From there you can click on the plan you want, and click "Force Plan" to force it: