gd qry to identify running query inside sp without profiler

[ranjan...@thomsonreuters.com]

SELECT SUBSTRING(st.text, ( r.statement_start_offset / 2 ) + 1,

              ( ( CASE WHEN r.statement_end_offset <= 0

                       THEN DATALENGTH(st.text)

              ELSE r.statement_end_offset END -

       r.statement_start_offset ) / 2 ) + 1) AS statement_text

FROM   sys.dm_exec_requests r

       CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

WHERE  session_id = 65

 

[Mushir]

Good one !

DBCC INPUTBUFFER(65) would give you all the statements from the same process but would not be able to identify what exactly is running at the moment.