query_store_query_text: Searching Query History by Statement Text

Searching sys.query_store_query_text by statement text is the fastest way to find a query in Query Store, but the text is only a doorway. The query_id and plan_id behind it hold the real history.

The “it was slow yesterday” question

Someone tells you a procedure was slow yesterday afternoon. You do not have a query_id. You have a table name and a hunch. Query Store keeps the text of every statement it captured, so a LIKE search is a fair place to start.

Let me set up something to find. The demo creates a database, turns on Query Store, and drops the database at the end. Capture mode ALL records every statement, which suits a demo. First check that Query Store is actually capturing.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
ALTER DATABASE CURRENT SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);
GO
SELECT actual_state_desc, query_capture_mode_desc
FROM sys.database_query_store_options;

You should see READ_WRITE and ALL. Do this check first in a real investigation. If the state is read-only, or capture is limited to expensive queries, a missing statement proves nothing. Absence in Query Store is not proof that something never ran.

Create something to search for

Here is a small table and a procedure that reads it. The procedure returns its value through an output parameter, so the loop below stays quiet. Then I run it 20 times and flush Query Store to disk so the catalog views see everything.

CREATE TABLE dbo.NoteDemo (ItemId int PRIMARY KEY, NoteText nvarchar(30));
INSERT dbo.NoteDemo (ItemId, NoteText) VALUES (1, N'Captured statement');
GO
CREATE PROCEDURE dbo.ReadNoteDemo @Id int, @Note nvarchar(30) OUTPUT
AS
SELECT @Note = NoteText FROM dbo.NoteDemo WHERE ItemId = @Id;
GO
DECLARE @n int = 0, @Note nvarchar(30);
WHILE @n < 20
BEGIN
    EXEC dbo.ReadNoteDemo @Id = 1, @Note = @Note OUTPUT;
    SET @n += 1;
END;
GO
EXEC sys.sp_query_store_flush_db;

Search by text, then read what came back

Now the search. I look for the table name, because that is all I would know in real life.

SELECT q.query_id, q.object_id, OBJECT_NAME(q.object_id) AS object_name, qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE N'%NoteDemo%'
ORDER BY q.query_id;

I expected one row. I got four. There was the INSERT I ran, a statistics statement that SQL Server issued by itself, the procedure statement, and the search query itself. Your list may differ a little, but expect more than one hit.

So a text fragment is not a unique request. The object_id and object_name columns help: only the procedure row has a real object. Keep the object_id and context_settings_id with each query_id when you narrow down.

SELECT qt.query_text_id, q.query_id, q.context_settings_id, qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON q.query_text_id = qt.query_text_id
WHERE q.object_id = OBJECT_ID(N'dbo.ReadNoteDemo')
ORDER BY q.query_id;

One row now, with the parameter list in front of the statement text. That query_id is your key to everything else.

Follow the identifiers to the runtime history

From the query_id you walk to its plans, then to the runtime statistics, then to the time interval they belong to. Duration is stored in microseconds, so I also divide by 1000.0 for milliseconds.

The average needs care. Each runtime row has its own average and execution count. So I weight each average by its count, instead of averaging the averages.

SELECT q.query_id, p.plan_id, i.start_time, i.end_time, r.execution_type_desc,
       SUM(r.count_executions) AS execution_total,
       SUM(r.avg_duration * r.count_executions)
           / NULLIF(SUM(r.count_executions), 0) AS average_microseconds,
       SUM(r.avg_duration * r.count_executions)
           / NULLIF(SUM(r.count_executions), 0) / 1000.0 AS average_ms
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS r ON r.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
     ON i.runtime_stats_interval_id = r.runtime_stats_interval_id
WHERE q.object_id = OBJECT_ID(N'dbo.ReadNoteDemo')
GROUP BY q.query_id, p.plan_id, i.start_time, i.end_time, r.execution_type_desc
ORDER BY q.query_id, p.plan_id, i.start_time, r.execution_type_desc;

My run shows one plan, one interval, execution type Regular, and 20 executions. That matches the 20 calls. The start and end times mark a one-hour window, and your values will differ. Group by plan, interval and execution type, because a busy interval can be split into several runtime rows.

Keep the interval with any number you report. A bare average duration means little without “between 2 and 3 PM yesterday”. And a tiny demo is no benchmark for your own query.

Finally, a history search is for diagnosis. Forcing a plan is a separate decision. Write down the query_id, plan_id and interval before you make it.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Next time someone says a query was slow, start with the text, but end with the identifiers.

A matching fragment is not a unique history, it is a doorway to query identifiers.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.