Hi Folks,
Sometimes you are running a rather large SQL stored procedure, and would like to know which part of the code is running and executing.
Below is a query you can use to see what code is executing at the current moment. I find this very useful, not only for detecting code blocks that are running, but also for identifying long running transaction and so forth.
|
select r.blocking_session_id,
r.session_id
,status
,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
,qt.dbid
,qt.objectid
,r.cpu_time
,r.total_elapsed_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
order by r.blocking_session_id desc, r.scheduler_id, r.status, r.session_id
|
Hope this helps with your queries and code re-factoring.
Like this:
Like Loading...
Related
Romiko Derbynew 