Why is sys.query_store_query_text not returning my query?

Query on AdventureWorks2019 database:

SELECT * FROM HumanResources.Department D INNER JOIN HumanResources.EmployeeDepartmentHistory E ON D.DepartmentID = E.DepartmentID

Question: Why the following query is not returning any data. I was expecting it to return at least one row for my above test query. What I may be missing or misunderstanding here?

select 
    query_text_id, 
    query_sql_text as query_sql_text 
from sys.query_store_query_text where query_sql_text like '%SELECT * FROM HumanResources.Department D INNER JOIN HumanResources.EmployeeDepartmentHistory E ON D.DepartmentID = E.DepartmentID%'

Ref: sys.query_store_query_text

Query store is turned on with the default configuration.

1. Query Store is not turned on

You can check the sys.databases DMV.
SELECT
d.name
, d.is_query_store_on
FROM sys.databases AS d
WHERE d.database_id = DB_ID()


1. Query Store is read-only

Run the query below in the context of the relevant database. The actual_state_desc should be READ_WRITE and readonly_reason = 0
SELECT
dqso.desired_state_desc
, dqso.actual_state_desc
, dqso.readonly_reason
, dqso.query_capture_mode_desc
FROM sys.database_query_store_options AS dqso


1. Query Store is fine, but the query didn't pass a threshold

The default Query_Capture_Mode for QS is Auto. I wrote in detail about Query Store defaults on my blog.
In short, the query must match at least one of these conditions to be saved.

• Execute 30 times

• OR total compile CPU time needs to be > 1s

• OR total execution CPU time needs to be > 100ms

all within 24 hours (default).

You can change the capture mode to ALL or match the above conditions.

1. Query Store is fine, the query passed a threshold, but the filter is wrong

There can be a typo in the query text you are trying to find. Or mismatched whitespace. Or you'll find the query you just used to find your query. Try to search for the smallest but distinct value.

Another option is to grab the Query hash from the estimated or actual plan (here's a guide from a different blog post) and then search the query store by the exact Query hash.

Example:
SELECT
qsq.query_id
, qsq.query_hash
, qsqt.query_sql_text
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE qsq.query_hash = 0x52272BBBB4DF6B47 -- use your query hash


StackExchange Database Administrators Q#318569, answer score: 8