Last active
August 29, 2015 14:15
-
-
Save jdaigle/281c331e1265425cd1d1 to your computer and use it in GitHub Desktop.
calc worker time for queries
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select | |
qs.creation_time, | |
qs.last_execution_time | |
,qs.execution_count | |
,(CAST(qs.execution_count as decimal(18,4)) / (SUM(qs.execution_count) OVER (PARTITION BY 1))) AS perc_execution_count | |
,qs.total_worker_time -- microseconds | |
,CAST(qs.total_worker_time as decimal(18,4)) / CAST(qs.execution_count AS float) as avg_total_worker_time | |
--,SUM(qs.total_worker_time) OVER (PARTITION BY 1) AS total_total_worker_time | |
,(CAST(qs.total_worker_time as decimal(18,4)) / (SUM(qs.total_worker_time) OVER (PARTITION BY 1))) AS perc_total_worker_time | |
,qs.total_elapsed_time -- microseconds | |
,CAST(qs.total_elapsed_time as decimal(18,2)) / CAST(qs.execution_count AS float) as avg_total_elapsed_time | |
--,SUM(qs.total_elapsed_time) OVER (PARTITION BY 1) AS total_total_elapsed_time | |
,(CAST(qs.total_elapsed_time as decimal(18,4)) / (SUM(qs.total_elapsed_time) OVER (PARTITION BY 1))) AS perc_total_elapsed_time | |
,qs.total_rows -- microseconds | |
,CEILING(CAST(qs.total_rows as decimal(18,4)) / qs.execution_count) as avg_total_rows | |
--,SUM(qs.total_rows) OVER (PARTITION BY 1) AS total_total_rows | |
,(CAST(qs.total_rows as decimal(18,4)) / (SUM(qs.total_rows) OVER (PARTITION BY 1))) AS perc_total_rows | |
,'"'+REPLACE(REPLACE(REPLACE (SUBSTRING(qt.text,qs.statement_start_offset/2 +1, | |
(CASE WHEN qs.statement_end_offset = -1 | |
THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 | |
ELSE qs.statement_end_offset end - | |
qs.statement_start_offset | |
)/2 | |
),'"','''' ), char(13), ''), char(10), ' ')+'"'AS query_text | |
from sys.dm_exec_query_stats qs | |
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment