New feature request for Toad-SS and it also may be useful in TDA.
Please add an option to results for advanced results which would give a
process/sessionid execution time in hrs., mins., secs., cpu_time, read, writes,
Logical_reads.
Please use the below script as a starting point for getting that
information… I find it useful in debugging scripts…
select r.session_id
, substring (qt.text,r.statement_start_offset / 2 ,
( case when r.statement_end_offset = - 1
then len ( convert (nvarchar( max ), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset) / 2 )
as query_text — this is the statement executing right now
,((r.total_elapsed_time / 1000 ) / 60 ) / 60 as ‘Hrs’
,(r.total_elapsed_time / 1000 ) / 60 - (((r.total_elapsed_time / 1000 ) / 60 ) /
60 ) * 60 as ‘Mins’
, r.total_elapsed_time / 1000 - ((r.total_elapsed_time / 1000 ) / 60 ) * 60 as
‘Secs’
,r.status
,wait_type
,r.blocking_session_id as blocked_by
,r.cpu_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
from sys.dm_exec_requests r
cross apply sys. dm_exec_sql_text (sql_handle) as qt
where r.session_id > 50
–and status=‘suspended’
order by total_elapsed_time desc , r.cpu_time desc
Hank Freeman
Senior SQL Server DBA/Data & Systems Architect
678-414-0090 my cell Primary
hank.freeman50 via Skype
