Início / dba / Perguntas / 201706
Accepted
uncaged
Asked: 2018-03-20 08:38:39 +0800 CST

Detalhes do "Consumo geral de recursos" do repositório de consultas

  • 772

Eu quero saber quanta CPU total um banco de dados consumiu para um determinado período, para todas as consultas.

Posso vê-lo no SSMS (em Repositório de consultas/Consumo geral de recursos), mas como posso consultar isso programaticamente?

sql-server query-store

1 respostas

  1. Best Answer

    A GUI do Query Store usa este 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'

    Os resultados se parecem com:

    ╔════════════════════════╦════════════════╦═══════ ═════════╦════════════════════════╦═══════════════ ══════════╦═════════════════════════╦═════════════ ═══╦═══════════╦═════════════════════════════╦════ ════════════╦════════════════════════════════╦════ ════════════════════════════╗
║ 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 ║ (Sem nome de coluna) ║ (Sem nome de coluna)
╠════════════════════════╬════════════════╬═══════ ═════════╬════════════════════════╬═══════════════ ══════════╬═════════════════════════╬═════════════ ═══╬═══════════╬═════════════════════════════╬════ ════════════╬════════════════════════════════╬════ ════════════════════════════╣
║ 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 ║
╚════════════════════════╩════════════════╩═══════ ═════════╩════════════════════════╩═══════════════ ══════════╩═════════════════════════╩═════════════ ═══╩═══════════╩═════════════════════════════╩════ ════════════╩════════════════════════════════╩════ ════════════════════════════╝

    Eu vi isso usando um rastreamento de instrução T-SQL no servidor enquanto abria simultaneamente a GUI para "Consumo geral de recursos" na minha instância local do SQL Server 2016.

    Eu refatorei a consulta acima um pouco, para:

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

    Isso retorna uma linha para cada dia entre as datas de término de início fornecidas.

    • 2

relate perguntas