主页 / dba / 问题 / 201706
Accepted
uncaged
Asked: 2018-03-20 08:38:39 +0800 CST

查询存储“整体资源消耗”详细信息

  • 772

我想知道在给定日期范围内，对于所有查询，数据库消耗了多少 CPU 总量。

我可以在 SSMS 中看到它（在查询存储/总体资源消耗下），但我如何以编程方式查询它？

sql-server query-store

1 个回答

  1. Best Answer

    查询存储 GUI 使用此 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'

    结果如下：

    ╔════════════════════════╦════════════════╦═══════ ═════════╦════════════════════════╦═══════════════ ══════════╦═════════════════════════╦═════════════ ═══╦═══════════╦═════════════════════════════╦════ ════════════╦════════════════════════════════╦════ ════════════════════════════╗
║ 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 ║ （无列名） ║ （无列名）
╠════════════════════════╬════════════════╬═══════ ═════════╬════════════════════════╬═══════════════ ══════════╬═════════════════════════╬═════════════ ═══╬═══════════╬═════════════════════════════╬════ ════════════╬════════════════════════════════╬════ ════════════════════════════╣
║ 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:06:00 ║
╚════════════════════════╩════════════════╩═══════ ═════════╩════════════════════════╩═══════════════ ══════════╩═════════════════════════╩═════════════ ═══╩═══════════╩═════════════════════════════╩════ ════════════╩════════════════════════════════╩════ ════════════════════════════╝

    我看到在我的本地 SQL Server 2016 实例上使用 T-SQL 语句跟踪服务器，同时打开“总体资源消耗”的 GUI。

    我将上面的查询重新分解为：

    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);

    这会为提供的开始结束日期之间的每一天返回一行。

    • 2

相关问题