What SQL Text is that Session Running?
Use the code below if you have a long, complicated, stored procedure or piece of dynamic SQL running on a server, and you’d like to see exactly which piece of SQL Text, or code, is executing.
Not a lot of sql text in this painting.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
;WITH src AS ( SELECT [start] = r.statement_start_offset / 2 , [len] = r.statement_end_offset / 2 - r.statement_start_offset / 2 , [stmt] = t.text , [db] = d.name , [plan] = qp.query_plan , s.session_id , r.command , r.last_wait_type , r.wait_type , r.wait_time , r.blocking_session_id FROM sys.dm_exec_sessions S INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id INNER JOIN sys.databases d ON r.database_id = d.database_id OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) qp WHERE s.is_user_process = 1 AND s.session_id <> @@SPID ) SELECT SessionID = src.session_id , Command = src.command , [Database] = src.db , [Statement] = SUBSTRING( src.stmt , CASE WHEN src.[start] = 0 THEN 0 ELSE src.[start] END , CASE WHEN src.[len] = -1 THEN LEN(src.stmt) ELSE src.[len] END ) , [Plan] = src.[plan] , src.wait_type , src.wait_time , src.last_wait_type , src.blocking_session_id FROM src ORDER BY src.session_id; |
The output includes the exact statement that is being executed at the time the query runs. The cached execution plan is also shown, if available, along with some other nice details such as the database context, and command type.
This is a lightweight simple piece of code I use when I can’t install Adam Machanic’s sp_whoisactive monitoring system on a server. I’d highly recommend looking at sp_whoisactive since it’s a really thorough and useful utility.
Check out the rest of our tools for SQL Server.