主页 / dba / 问题 / 330672
Accepted
Francesco Mantovani
Asked: 2023-08-28 19:20:46 +0800 CST

计算total_worker_time（以分钟为单位）

  • 772

我有一个简单的查询，可以通过 query_plan_hash 和 query_hash显示前 10 个经过的时间：

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

我如何计算：

  • 总工作时间
  • 总经过时间
  • 逻辑读取总数

以分钟而不是毫秒为单位？

query-performance

2 个回答

  1. Best Answer

    您可能会发现此解决方案更干净。

    您可以将毫秒添加到某个时间点（在本例中19000101），然后使用 114 样式和Convert来返回，而不是一遍又一遍地进行大量数学运算并将它们连接在一起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. 谢谢@MartinSmith 提醒我现在total_logical_reads还不是时候。

    我在这里找到了解决方案：

    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

相关问题