May 15, 2015 at 1:12 pm
Comments posted to this topic are about the item Query Performance Report
June 2, 2015 at 7:36 am
Thanks, I'll give it a try.
November 16, 2015 at 4:59 am
Awesome script ... thanks.
I've added support for dbid column when raw sql is executed which is not wrapped through a sp.
Maybe you can change your script that way.
I've also added 2 more (optional) columns which return database name and the inner sql command text, if needed.
SELECT s.[sql_handle]
, s.[statement_start_offset]
, s.[statement_end_offset]
, s.[plan_generation_num]
, s.[plan_handle]
, s.[creation_time]
, s.[last_execution_time]
, s.[execution_count]
, s.[total_worker_time]
, s.[last_worker_time]
, s.[min_worker_time]
, s.[max_worker_time]
, s.[total_physical_reads]
, s.[last_physical_reads]
, s.[min_physical_reads]
, s.[max_physical_reads]
, s.[total_logical_writes]
, s.[last_logical_writes]
, s.[min_logical_writes]
, s.[max_logical_writes]
, s.[total_logical_reads]
, s.[last_logical_reads]
, s.[min_logical_reads]
, s.[max_logical_reads]
, s.[total_clr_time]
, s.[last_clr_time]
, s.[min_clr_time]
, s.[max_clr_time]
, s.[total_elapsed_time]
, s.[last_elapsed_time]
, s.[min_elapsed_time]
, s.[max_elapsed_time]
, s.[query_hash]
, s.[query_plan_hash]
, s.[total_rows]
, s.[last_rows]
, s.[min_rows]
, s.[max_rows]
, st.[text]
, pa.value AS [dbid]
, q.objectid
, q.number
, q.encrypted
, q.query_plan
-- Additional Columns: DBName & CmdText if needed
--, COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value as int)) + '*', 'Resource') AS DBNAME
--, SUBSTRING(text,
---- starting value for substring
-- CASE WHEN statement_start_offset = 0
-- OR statement_start_offset IS NULL
-- THEN 1
-- ELSE statement_start_offset/2 + 1 END,
---- ending value for substring
-- CASE WHEN statement_end_offset = 0
-- OR statement_end_offset = -1
-- OR statement_end_offset IS NULL
-- THEN LEN(text)
-- ELSE statement_end_offset/2 END -
-- CASE WHEN statement_start_offset = 0
-- OR statement_start_offset IS NULL
-- THEN 1
-- ELSE statement_start_offset / 2 END + 1
--) AS TSQL
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
CROSS APPLY sys.dm_exec_query_plan (plan_handle) q
WHERE pa.attribute = 'dbid'
And last_execution_time > GETDATE()-1
February 25, 2016 at 4:26 pm
Thanks for the script.
April 27, 2016 at 6:59 am
This works out pretty nice, thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply