13

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.

2 Answers 2

20

One way to do this is to query the Query Store views directly for the info you've gained from the plan cache:

SELECT 
    qsq.query_id,
    qsq.last_execution_time,
    qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt
        ON qsq.query_text_id = qsqt.query_text_id
WHERE
    qsqt.query_sql_text LIKE '%your query text%';

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:

screenshot of tracked queries tab and text box

From there you can click on the plan you want, and click "Force Plan" to force it:

Screenshot of force plan button

10

The UI is super unfriendly, but you can also search query text directly from the "Tracked Queries" dashboard. Given my garbage database with only 2 system queries in it...

select 
    query_text_id, 
    left(query_sql_text,50) as query_sql_text 
from sys.query_store_query_text

...showing...

+---------------+----------------------------------------------------+
| query_text_id | query_sql_text                                     |
+---------------+----------------------------------------------------+
| 1             | (@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4 |
| 2             | SELECT db.name as HasMemoryOptimizedObjects from m |
+---------------+----------------------------------------------------+

..I can click on the "Magnifying Glass 🔎" icon in the "Tracking Query" search box...

Magnifying Glass icon on Tracking Query input bar

This will pop out the following input form...

Query Text search input form. A red box encloses the search field. A blue box encloses the magnifying glass icon.

If you input the query test you want to search into the search bar (boxed in red in the screencap) and the press enter or click the magnifying glass icon (boxed in blue), you can browse Query ID, Query Text ID, and Query Text from a builtin UI (unfriendly though it may be to access).

By way of example, you can see in the following screen cap I have searched the letter "a" and get back both queries...

Searching the letter "a" in the input form

...while in the next screen cap I have searched the more selective string "HasMemoryOptimizedObjects", which returns only the matching query text

Searching the string "HasMemoryOptimizedObjects" in the input form

6
  • I didn't know about this 😮 Commented Mar 31, 2020 at 20:19
  • 1
    @JoshDarnell they do a good job of hiding it 😜 Commented Mar 31, 2020 at 20:41
  • How to honor an equally correct answer with just another approach? :)
    – MHeld
    Commented Apr 2, 2020 at 6:27
  • Shame you can't search by query hash (which is what Azure SQL Analytics keeps giving me). Commented Apr 2, 2021 at 16:49
  • @Simon_Weaver if you check the docs for the query_store_query system view referenced in the accepted answer, you'll find query_hash is a column exposed in this set 🙂 Commented Apr 5, 2021 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.