How to Search for Specific Queries in Query Store in SQL Server

SQL Server’s Query Store can accumulate hundreds or even thousands of queries over time. When troubleshooting a specific performance issue or investigating a particular query, you’ll probably want to search the captured data to find what you’re looking for.

To search for queries containing specific text, you can query the sys.query_store_query_text view:

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

This finds all queries that reference “Orders” anywhere in their SQL text.

Searching by Table Name

To find all queries that access a specific table, we can do something like this:

SELECT 
    q.query_id,
    qt.query_sql_text,
    q.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE '%FROM Customers%'
   OR qt.query_sql_text LIKE '%JOIN Customers%'
ORDER BY q.last_execution_time DESC;

This searches for queries that select from or join to the “Customers” table.

Searching by Stored Procedure Name

To find queries from a specific stored procedure:

SELECT 
    q.query_id,
    qt.query_sql_text,
    OBJECT_NAME(q.object_id) AS procedure_name,
    q.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE OBJECT_NAME(q.object_id) = 'SearchForCustomers'
ORDER BY q.last_execution_time DESC;

This returns all queries that originated from the “SearchForCustomers” stored procedure.

Searching by Query ID

If you know the specific query ID you’re looking for:

SELECT 
    q.query_id,
    qt.query_sql_text,
    q.initial_compile_start_time,
    q.last_compile_start_time,
    q.last_execution_time,
    OBJECT_NAME(q.object_id) AS object_name
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE q.query_id = 123;

Replace 123 with your actual query ID.

Searching with Multiple Criteria

To narrow results with multiple search terms:

SELECT 
    q.query_id,
    qt.query_sql_text,
    q.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE '%Orders%'
  AND qt.query_sql_text LIKE '%Status%'
  AND qt.query_sql_text LIKE '%Pending%'
  AND q.last_execution_time >= DATEADD(day, -7, GETUTCDATE())
ORDER BY q.last_execution_time DESC;

This finds queries containing “Orders”, “Status”, and “Pending” that executed in the last 7 days.

Case-Sensitive Searching

By default, LIKE searches are case-insensitive (depending on your database collation). For case-sensitive searches:

SELECT 
    q.query_id,
    qt.query_sql_text,
    q.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text COLLATE Latin1_General_CS_AS LIKE '%Orders%'
ORDER BY q.last_execution_time DESC;

This performs a case-sensitive search using the Latin1_General_CS_AS collation.

Searching Query Text Previews

For long queries, you might want to see just a preview:

SELECT 
    q.query_id,
    LEFT(qt.query_sql_text, 100) + '...' AS query_preview,
    LEN(qt.query_sql_text) AS query_length,
    q.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE '%Orders%'
ORDER BY q.last_execution_time DESC;

This shows the first 100 characters of each query along with its total length, making it easier to scan through results.