Skip to content

Instantly share code, notes, and snippets.

@jdaigle
Last active August 29, 2015 14:15
Show Gist options
  • Save jdaigle/281c331e1265425cd1d1 to your computer and use it in GitHub Desktop.
Save jdaigle/281c331e1265425cd1d1 to your computer and use it in GitHub Desktop.
calc worker time for queries
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