SQL Server 2016 Query Store – How to Find Specific Queries

query-storesql-server-2016

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:

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