Toad World® Forums

New Feature request for Toad-SS Results ( Advanced Results)

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

hfreeman@msn.com

678-414-0090 my cell Primary

hank.freeman50 via Skype

image001.jpeg

image001.gif

image002.jpeg
image001.jpeg