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

1. http://dotnetslackers.com/articles/sql/Identifying-currently-running-SQL-queries.aspx

Identifying currently running SQL queries

SELECT  

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  

  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2  

  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

2. What's blocking my running SQL?  http://dotnetslackers.com/articles/sql/Whats-blocking-my-running-SQL.aspx

SELECT 
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