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.
(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
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.