3

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.

0

1 Answer 1

8

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()

2. 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

3. 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.

4. 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
2
  • 1
    Your suggestion (in your third list item) of changing the Query Store Capture mode to All worked (thank you). Mine was set to default (that is 'auto') and the query execution being way too fast -on a super fast Azure SQL Managed Instance where I was the only user- was not passing the threshold you described in your list item 3. I liked the way you outlined various scenarios as it should help other readers of this post, as well.
    – nam
    Oct 23, 2022 at 16:11
  • I have a question posted here in case you have time to comment/suggest a possible solution.
    – nam
    Jan 24, 2023 at 15:54

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.