Calculate total_worker_time in minutes [closed]

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'
,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

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,
(
MICROSECOND,
SUM(qs.total_worker_time),
CONVERT(datetime2, '19000101')
),
'19000101'
)
),
2
) +
' ' +
CONVERT
(
varchar(12),
(
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,
(
MICROSECOND,
SUM(qs.total_worker_time),
CONVERT(datetime2, '19000101')
),
'19000101'
)
),
2
) +
' ' +
CONVERT
(
varchar(12),
(
MICROSECOND,
SUM(qs.total_elapsed_time),
CONVERT(datetime2, '19000101')
),
114
),
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;
``````

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]
,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
``````