Início / dba / Perguntas / 330672
Accepted
Francesco Mantovani
Asked: 2023-08-28 19:20:46 +0800 CST

Calcule total_worker_time em minutos

  • 772

Eu tenho uma consulta fácil que pode me mostrar os 10 principais tempos decorridos por query_plan_hash e query_hash :

PRINT '-- top 10 elapsed time  by query_plan_hash and query_hash --'

SELECT TOP 10 query_plan_hash
    ,query_hash
    ,sum(execution_count) AS 'execution_count'
    ,sum(total_worker_time) AS 'total_worker_time'
    ,SUM(total_elapsed_time) AS 'total_elapsed_time'
    ,SUM(total_logical_reads) AS 'total_logical_reads'
    ,max(REPLACE(REPLACE(REPLACE(SUBSTRING(CONVERT(NVARCHAR(4000), st.[text]), qs.statement_start_offset / 2 + 1, CASE 
                            WHEN qs.statement_end_offset = - 1
                                THEN LEN(st.[text])
                            ELSE qs.statement_end_offset / 2 - qs.statement_start_offset / 2 + 1
                            END), CHAR(13), ' '), CHAR(10), ' '), CHAR(09), ' ')) AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
GROUP BY query_plan_hash
    ,query_hash
ORDER BY sum(total_elapsed_time) DESC;
GO

Como posso calcular:

  • total_worker_time
  • total_tempo_decorrido
  • leituras_lógicas_totais

em minutos em vez de milissegundos?

query-performance

2 respostas

  1. Best Answer

    Você pode achar esta solução mais limpa.

    Em vez de fazer um monte de matemática repetidamente e concatenar as coisas, você pode adicionar os milissegundos a um ponto no tempo (neste caso 19000101) e então usar o estilo 114 com convert para retornar hh:mi:ss:mmm.

    SELECT TOP (10)
    qs.query_plan_hash,
    qs.query_hash,
    execution_count = SUM(qs.execution_count),
    total_worker_time = SUM(qs.total_worker_time),
    [total_worker_time_formatted (dd hh:mm:ss.mss)] =
        RIGHT
        (
            '00' +
            CONVERT
            (
                varchar(10),
                DATEDIFF
                (
                    DAY,
                    DATEADD
                    (
                        MICROSECOND,    
                        SUM(qs.total_worker_time),    
                        CONVERT(datetime2, '19000101')
                    ),
                    '19000101'
                )
            ),
            2
        ) +
        ' ' +
        CONVERT   
        (   
            varchar(12),    
            DATEADD   
            (   
                MICROSECOND,    
                SUM(qs.total_worker_time),    
                CONVERT(datetime2, '19000101')
            ),    
            114   
        ),
    total_elapsed_time = SUM(qs.total_elapsed_time),
    [total_elapsed_time_formatted (dd hh:mm:ss.mss)] =
        RIGHT
        (
            '00' +
            CONVERT
            (
                varchar(10),
                DATEDIFF
                (
                    DAY,
                    DATEADD
                    (
                        MICROSECOND,    
                        SUM(qs.total_worker_time),    
                        CONVERT(datetime2, '19000101')
                    ),
                    '19000101'
                )
            ),
            2
        ) +
        ' ' +
        CONVERT   
        (   
            varchar(12),    
            DATEADD   
            (   
                MICROSECOND,    
                SUM(qs.total_elapsed_time),    
                CONVERT(datetime2, '19000101')
            ),    
            114   
        ),
    total_logical_reads = SUM(qs.total_logical_reads),
    sample_statement_text =
        MAX
        (
            REPLACE(REPLACE(REPLACE(
            SUBSTRING
            (
                st.text,
                qs.statement_start_offset / 2 + 1,
                CASE
                    qs.statement_end_offset
                    WHEN -1
                    THEN LEN(st.text)
                    ELSE qs.statement_end_offset / 2
                          - qs.statement_start_offset / 2 + 1
                END
            ),
            CHAR(13), ''), CHAR(10), ''), CHAR(09), '')
        )
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
GROUP BY
    qs.query_plan_hash,
    qs.query_hash
ORDER BY
    SUM(total_elapsed_time) DESC;
    • 2

  2. Obrigado @MartinSmith por me lembrar que total_logical_readsnão é hora.

    Encontrei a solução aqui :

    SELECT TOP 10 query_plan_hash
    ,query_hash
    ,sum(execution_count) AS 'execution_count'
    --,sum(total_worker_time) AS 'total_worker_time'
    ,CONCAT(
        RIGHT('0' + CAST(sum(total_worker_time)/(1000*60*60) AS VARCHAR(2)),2), ':',                     -- Hrs
        RIGHT('0' + CAST((sum(total_worker_time)%(1000*60*60))/(1000*60) AS VARCHAR(2)),2), ':',         -- Mins
        RIGHT('0' + CAST(((sum(total_worker_time)%(1000*60*60))%(1000*60))/1000 AS VARCHAR(2)),2), '.',  -- Secs
        ((sum(total_worker_time)%(1000*60*60))%(1000*60))%1000                                           -- Milli Secs
) AS [total_worker_time in hh:mm:ss.ms]
    --,SUM(total_elapsed_time) AS 'total_elapsed_time'
    ,CONCAT(
        RIGHT('0' + CAST(sum(total_elapsed_time)/(1000*60*60) AS decimal(38,0)),2), ':',                     -- Hrs
        RIGHT('0' + CAST((sum(total_elapsed_time)%(1000*60*60))/(1000*60) AS decimal(38,0)),2), ':',         -- Mins
        RIGHT('0' + CAST(((sum(total_elapsed_time)%(1000*60*60))%(1000*60))/1000  AS decimal(38,0)),2), '.',  -- Secs
        ((sum(total_elapsed_time)%(1000*60*60))%(1000*60))%1000                                           -- Milli Secs
) AS [total_elapsed_time in hh:mm:ss.ms]
    ,SUM(total_logical_reads) AS 'total_logical_reads'
    ,max(REPLACE(REPLACE(REPLACE(SUBSTRING(CONVERT(NVARCHAR(4000), st.[text]), qs.statement_start_offset / 2 + 1, CASE 
                            WHEN qs.statement_end_offset = - 1
                                THEN LEN(st.[text])
                            ELSE qs.statement_end_offset / 2 - qs.statement_start_offset / 2 + 1
                            END), CHAR(13), ' '), CHAR(10), ' '), CHAR(09), ' ')) AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
GROUP BY query_plan_hash
    ,query_hash
ORDER BY sum(total_elapsed_time) DESC;
GO
    • 0

relate perguntas