With SQL Server’s Query Store, how can cleanup avoid deleting “least expensive” queries?

Posted on

Question :

I’m only interested in resource consumption (QUERY_CAPTURE_MODE = AUTO), with a fairly short interval (e.g. INTERVAL_LENGTH_MINUTES = 5). I’ll be grabbing data from the query store on a fairly regular basis, such as daily, retrieving stats of all completed intervals not previously retrieved. My concern is that the docs say “Size-based cleanup removes the least expensive and oldest queries first.” I certainly want to discard stats of old queries (older than my retrieval interval), but if a ton of low-resource queries were performed in the intervals I haven’t yet retrieved, I don’t want those discarded, because they could add up to a high total consumption.

Is there any way to force deletion of stats for old queries, or something else I can do to assure that when cleanup occurs, that it doesn’t delete stats for intervals I haven’t retrieved yet?

Answer :

You can also use STALE_QUERY_THRESHOLD_DAYS to clean purely based on time rather than the size-based cleanup parameter.

Leave a Reply

Your email address will not be published. Required fields are marked *