Plan Summary for single query ( Resource Consumers, Memory Consumption, Total )

gistfile1.txt

exec sp_executesql N'WITH 
    bucketizer as 
    (
        SELECT
            rs.plan_id as plan_id,
            rs.execution_type as execution_type,
            SUM(rs.count_executions) count_executions,
            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,
            ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*8,2) as avg_query_max_used_memory,
            ROUND(CONVERT(float, MAX(rs.max_query_max_used_memory))*8,2) as max_query_max_used_memory,
            ROUND(CONVERT(float, MIN(rs.min_query_max_used_memory))*8,2) as min_query_max_used_memory,
            ROUND(CONVERT(float, SQRT( SUM(rs.stdev_query_max_used_memory*rs.stdev_query_max_used_memory*rs.count_executions)/NULLIF(SUM(rs.count_executions), 0)))*8,2) as stdev_query_max_used_memory,
            ISNULL(ROUND(CONVERT(float, (SQRT( SUM(rs.stdev_query_max_used_memory*rs.stdev_query_max_used_memory*rs.count_executions)/NULLIF(SUM(rs.count_executions), 0))*SUM(rs.count_executions)) / NULLIF(SUM(rs.avg_query_max_used_memory*rs.count_executions), 0)),2), 0) as variation_query_max_used_memory,
            ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) as total_query_max_used_memory
        FROM
            sys.query_store_runtime_stats rs
            JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
        WHERE
            p.query_id = @query_id
        AND NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
        GROUP BY
            rs.plan_id,
            rs.execution_type,
            DATEDIFF(d, 0, rs.last_execution_time)
    ),
    is_forced as
    (
        SELECT is_forced_plan, plan_id
          FROM sys.query_store_plan
    )
SELECT b.plan_id as plan_id,
    is_forced_plan,
    execution_type,
    count_executions,
    SWITCHOFFSET(bucket_start, DATEPART(tz, @interval_start_time)) AS bucket_start,
    SWITCHOFFSET(bucket_end, DATEPART(tz, @interval_start_time)) AS bucket_end,
    avg_query_max_used_memory,
    max_query_max_used_memory,
    min_query_max_used_memory,
    stdev_query_max_used_memory,
    variation_query_max_used_memory,
    total_query_max_used_memory
FROM bucketizer b
JOIN is_forced f ON f.plan_id = b.plan_id',N'@query_id bigint,@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',@query_id=3953,@interval_start_time='2023-12-27 15:03:22.2293087 -06:00',@interval_end_time='2024-01-03 15:03:22.2293087 -06:00'