Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- exec sp_executesql N'WITH DateGenerator AS
- (
- SELECT CAST(@interval_start_time AS DATETIME) DatePlaceHolder
- UNION ALL
- SELECT DATEADD(d, 1, DatePlaceHolder)
- FROM DateGenerator
- WHERE DATEADD(d, 1, DatePlaceHolder) < @interval_end_time
- ),
- UnionAll AS
- (
- SELECT
- CONVERT(float, SUM(rs.count_executions)) as total_count_executions,
- ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) as total_duration,
- ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) as total_cpu_time,
- ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) as total_logical_io_reads,
- ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) as total_logical_io_writes,
- ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) as total_physical_io_reads,
- ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2) as total_clr_time,
- ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0) as total_dop,
- ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) as total_query_max_used_memory,
- ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0) as total_rowcount,
- DATEADD(d, ((DATEDIFF(d, 0, rs.last_execution_time))),0 ) as bucket_start,
- DATEADD(d, (1 + (DATEDIFF(d, 0, rs.last_execution_time))), 0) as bucket_end
- FROM sys.query_store_runtime_stats rs
- WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
- GROUP BY DATEDIFF(d, 0, rs.last_execution_time)
- )
- SELECT
- total_count_executions,
- total_duration,
- total_cpu_time,
- total_logical_io_reads,
- total_logical_io_writes,
- total_physical_io_reads,
- total_clr_time,
- total_dop,
- total_query_max_used_memory,
- total_rowcount,
- SWITCHOFFSET(bucket_start, DATEPART(tz, @interval_start_time)) , SWITCHOFFSET(bucket_end, DATEPART(tz, @interval_start_time))
- FROM
- (
- SELECT *, ROW_NUMBER() OVER (PARTITION BY bucket_start ORDER BY bucket_start, total_duration DESC) AS RowNumber
- FROM UnionAll
- ) as UnionAllResults
- WHERE UnionAllResults.RowNumber = 1
- OPTION (MAXRECURSION 0)',N'@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',@interval_start_time='2018-02-19 10:41:32.4646188 -06:00',@interval_end_time='2018-03-19 11:41:32.4646188 -05:00'
Add Comment
Please, Sign In to add comment