2

I want to know how much total CPU a database has consumed for a given date span, for all queries.

I can see it in SSMS (under Query Store / Overall Resource Consumption), but how can I query for that programmatically?

2

The Query Store GUI uses this T-SQL:

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'

The results look like:

╔════════════════════════╦════════════════╦════════════════╦════════════════════════╦═════════════════════════╦═════════════════════════╦════════════════╦═══════════╦═════════════════════════════╦════════════════╦════════════════════════════════╦════════════════════════════════╗
║ 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 ║        (No column name)        ║        (No column name)        ║
╠════════════════════════╬════════════════╬════════════════╬════════════════════════╬═════════════════════════╬═════════════════════════╬════════════════╬═══════════╬═════════════════════════════╬════════════════╬════════════════════════════════╬════════════════════════════════╣
║                      2 ║           3.25 ║           3.25 ║                      0 ║                       0 ║                       0 ║              0 ║         2 ║                           0 ║              1 ║ 2018-03-18 18:00:00.000 -06:00 ║ 2018-03-19 18:00:00.000 -06:00 ║
╚════════════════════════╩════════════════╩════════════════╩════════════════════════╩═════════════════════════╩═════════════════════════╩════════════════╩═══════════╩═════════════════════════════╩════════════════╩════════════════════════════════╩════════════════════════════════╝

I saw that using a T-SQL statement trace against the server while simultaneous opening the GUI for "Overall Resource Consumption" on my local SQL Server 2016 instance.

I re-factored that above query down a bit, to:

DECLARE @interval_start_time datetimeoffset(7);
DECLARE @interval_end_time datetimeoffset(7);
SET @interval_start_time = '2018-02-19 10:41:32.4646188 -06:00';
SET @interval_end_time = '2018-03-19 11:41:32.4646188 -05:00';

SELECT
      total_count_executions = CONVERT(float, SUM(rs.count_executions))
    , total_duration = ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2)
    , total_cpu_time = ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2)
    , total_logical_io_reads = ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2)
    , total_logical_io_writes = ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2)
    , total_physical_io_reads = ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2)
    , total_clr_time = ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2)
    , total_dop = ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0)
    , total_query_max_used_memory = ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2)
    , total_rowcount = ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0)
    , bucket_start = DATEADD(d, ((DATEDIFF(DAY, 0, rs.last_execution_time))),0 )
    , bucket_end = DATEADD(d, (1 + (DATEDIFF(DAY, 0, rs.last_execution_time))), 0)
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(DAY, 0, rs.last_execution_time)
ORDER BY DATEDIFF(DAY, 0, rs.last_execution_time);

That returns a row for each day between the start end end dates provided.

  • I don't know about that! However, you're welcome! – Max Vernon Mar 19 '18 at 19:44

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.