Calculate total_worker_time in minutes [closed]

Question

I have an easy query which can show me the top 10 elapsed time by query_plan_hash and 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

How can I calculate:

• total_worker_time
• total_elapsed_time
• total_logical_reads

in minutes instead of milliseconds?

Answer 1

You may find this solution cleaner.

Rather than doing a bunch of math over and over again and concatenating things together, you can add the milliseconds to a point in time (in this case 19000101) and then the use 114 style with convert to return hh:mi:ss:mmm instead.

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;

Answer 2

Thank you @MartinSmith for reminding me that total_logical_reads is no a time.

I found the solution here:

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