January 6, 2023 at 5:46 pm
Hi,
I have a question related to the Query Store (SQL 2019).
The Query Store aggregates query runtime statistics in intervals, typically 30-60 min of length, depending on how the Query Store is configured by the user.
How do long-running queries, ie. queries that run for several hrs and span multiple runtime-stats intervals, get captured and aggregated?
For example, suppose that a query started execution at 9:20 am and completed execution at 11:40 pm. Query duration: 2 hrs and 40 min. Also the Query Store is configured with a runtimestats interval of 60 min.
When looking at the data in the [sys].[query_store_runtime_stats] catalog view, what will be the values of [first_execution_time], [last_execution_time] and in which time-slot interval will query be recorded:
9:00 to 10:00 am, 10:00 to 11:00 am or 11:00 am to 12:00 pm?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 7, 2023 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 10, 2023 at 12:23 pm
The first execution time is stored with the query, and isn't the start time. Instead, it's the time of the first execution of that query. That value could be weeks in the past if that query has been active every day. The last execution will be the last time the query completed executing, and will reflect the finish, not the start or middle of execution. Since there are not single executions (unless there is only, ever a single execution) of a query, but aggregations, the aggregates occur when the query completes. So, if it started at 8am, but doesn't finish until 3pm, the record of its completion will be in the 3pm aggregation.
Hope that helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2023 at 8:32 am
Interesting question. Good answer. Panel Dave has good articles on long running queries and query store SQL server features.
DBASupport
January 17, 2023 at 4:38 pm
Thanks, I actually was able to test this, and indeed the times reflect the completion of the query. Mystery solved!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply