4

I'm interested in the overall CPU utilization for a database in a time span. I see how to get that, but I'm concerned about the query store retention settings.

I don't need detailed info about each query execution, but because of the FK from sys.query_store_runtime_stats to sys.query_store_plan, it appears that the overall resource consumption isn't aggregated in total, but only per-plan. My concern is that if plans are dropped because of a Query Store Capture Mode of Auto and a Size-Based Cleanup Mode of Auto, then I think that means that the execution times for dropped queries won't be included in the totals for an interval.

The docs say "Infrequent queries and queries with insignificant compile and execution duration are ignored", and what worries me there is the word "and". Will it drop a 1-time query that uses tons of CPU? Since "Thresholds for execution count, compile and runtime duration are internally determined.", I don't know.

1

In my experience, no it will not drop that 1 time cpu heavy query

It may have an insignificant compile time, but the execution is not insignificant

But if you are interested in a very accurate measurement of total cpu utilization that includes every request, you should not use a Query Store Capture Mode of 'Auto', then it should be 'All'

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.