/************************************************************
* * k.moskvichev ©
* Time: 27.06.2019 13:13:52
************************************************************/
SELECT
creation_time
,last_execution_time
,execution_count
,total_worker_time / 1000 AS CPU
,CONVERT(MONEY ,(total_worker_time)) / (execution_count * 1000) AS [AvgCPUTime]
,qs.total_elapsed_time / 1000 AS TotDuration
,CONVERT(MONEY ,(qs.total_elapsed_time)) / (execution_count * 1000) AS [AvgDur]
,total_logical_reads AS [Reads]
,total_logical_writes AS [Writes]
,total_logical_reads + total_logical_writes AS [AggIO]
,CONVERT(
MONEY
,(total_logical_reads + total_logical_writes) / (execution_count + 0.0)
) AS [AvgIO]
,CASE
WHEN sql_handle IS NULL THEN ' '
ELSE (
SUBSTRING(
st.text
,(qs.statement_start_offset + 2) / 2
,(
CASE
WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX) ,st.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2
)
)
END AS query_text
,DB_NAME(st.dbid) AS database_name
,object_schema_name(st.objectid ,st.dbid) + '.' + OBJECT_NAME(st.objectid ,st.dbid) AS OBJECT_NAME
,qp.query_plan
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp --WHERE --creation_time > '2018-12-14 08:00:00.000' --object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) = 'dbo.ChequeExciseInsert' ORDER BY creation_time DESC
Similar Posts: