Saturday, June 25, 2011

SQL : What is going under the hood ? Very good articles to get info about current SQL activities


Identifying currently running SQL queries

es.session_id, es.host_name, es.login_name   
, er.status, DB_NAME(database_id) AS DatabaseName   
, SUBSTRING (qt.text,(er.statement_start_offset/2) + 1,  
  ((CASE WHEN er.statement_end_offset = -1  
  ELSE er.statement_end_offset 
  END - er.statement_start_offset)/2) + 1) AS [Individual Query]  
, qt.text AS [Parent Query]  
, es.program_name, er.start_time, qp.query_plan 
, er.wait_type, er.total_elapsed_time, er.cpu_time, er.logical_reads 
, er.blocking_session_id, er.open_transaction_count, er.last_wait_type 
, er.percent_complete 
FROM sys.dm_exec_requests AS er 
INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id 
CROSS APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt 
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp 
WHERE es.is_user_process=1  
AND es.session_Id NOT IN (@@SPID) 
ORDER BY es.session_id

Blocking.session_id as BlockingSessionId 
, Sess.login_name AS BlockingUser  
, BlockingSQL.text AS BlockingSQL 
, Waits.wait_type WhyBlocked 
, Blocked.session_id AS BlockedSessionId 
, USER_NAME(Blocked.user_id) AS BlockedUser 
, BlockedSQL.text AS BlockedSQL 
, DB_NAME(Blocked.database_id) AS DatabaseName 
FROM sys.dm_exec_connections AS Blocking 
INNER JOIN sys.dm_exec_requests AS Blocked  
ON Blocking.session_id = Blocked.blocking_session_id 
INNER JOIN sys.dm_os_waiting_tasks AS Waits  
ON Blocked.session_id = Waits.session_id 
RIGHT OUTER JOIN sys.dm_exec_sessions Sess  
ON Blocking.session_id = sess.session_id   
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL 
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL 
ORDER BY BlockingSessionId, BlockedSessionId

No comments:

Post a Comment