Displaying current SQL execution code

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

  • Uncategorized

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s