3

I see sometimes that queries are dropped from the query store. But I don't know why. The query store disk-space is not full and the query is far from the stale query treshold.

What I experience is that a query that I know has been executed has no history of getting executed in query store.

I will run this query to get the query_id:

SELECT   t.query_sql_text, q.query_id, object_name(q.object_id) AS parent_object, q.query_text_id, q.count_compiles
    FROM sys.query_store_query_text t JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id 
WHERE  t.query_sql_text LIKE  N'%part_of_query%'

And then I use the query_id in 'Tracked queries'.

But, sometimes, if I later in the day want to look at this query and run the above query again I get a new query_id and there is no trace of the old query_id. This can even impact 'Total resource consumption'. If the new query_id was created 11:00 and consumed a lot of resources, then it will look like there was a sudden increase in resource usage at 11:00.

The queries I often look at involve a temporary table, which means that there is a batch_sql_handle stored with the query_id. I know that database_id is part of this and that a failover on an Always On AG would cause a similar situation. ref: https://milossql.wordpress.com/2019/10/21/dude-wheres-my-forced-plan-part-1/ I have been trying to find more information about what the batch_sql_handle is based on and how the auto cleanup is working, but have not found anything that helps me.

Size Based Cleanup Mode is set to Auto

1
3

After setting up an extended events session looking at query store cleanup I saw my query_id in an event: query_store_size_retention_query_deleted

So my queries are getting dropped to avoid reacing size limit. flush_interval_settings was set to 1 min, which probably contributed to this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.