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

image

Hope this helps with your queries and code re-factoring.

Advertisements
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s