SQLSERVER 查询被阻塞进程的实际查询文本

SQLSERVER 查询被阻塞进程的实际查询文本:

SELECT    WT.session_idASwaiting_session_id,   (SELECT/*convert(varchar, c.connect_time,120)+' IP='+*/c.client_net_address+'/'+s.host_name+', '+s.login_name      FROMsys.dm_exec_connectionsc     INNERJOINsys.dm_exec_sessions sONs.session_id=c.session_id     WHERE c.session_id= WT.session_id)ASwaiting_session_info,    DB_NAME(TL.resource_database_id)AS DatabaseName,    WT.wait_duration_ms,    --WT.waiting_task_address, kill 1014    TL.request_mode,   (SELECTSUBSTRING(ST.text,(ER.statement_start_offset/2)+ 1,      ((CASE ER.statement_end_offset         WHEN-1THENDATALENGTH(ST.text)         ELSE ER.statement_end_offset        END- ER.statement_start_offset)/2)+ 1)      FROMsys.dm_exec_requestsAS ER     CROSSAPPLYsys.dm_exec_sql_text(ER.sql_handle)AS ST     WHERE ER.session_id= TL.request_session_id)      AS waiting_query_text,    TL.resource_type,    --TL.resource_associated_entity_id,    WT.wait_type,    WT.blocking_session_id,   (SELECT/*convert(varchar, c.connect_time,120)+' IP='+*/c.client_net_address+'/'+s.host_name+', '+s.login_name      FROMsys.dm_exec_connectionsc     INNERJOINsys.dm_exec_sessions sONs.session_id=c.session_id     WHERE c.session_id= WT.blocking_session_id)ASblocking_session_info,    --WT.resource_description AS blocking_resource_description,    CASE WHEN WT.blocking_session_id>0THEN      (SELECT ST2.textFROMsys.sysprocessesAS SP             CROSSAPPLYsys.dm_exec_sql_text(SP.sql_handle)AS ST2        WHERE SP.spid= WT.blocking_session_id)    ELSE NULL    END ASblocking_query_text   FROMsys.dm_os_waiting_tasksAS WT   JOINsys.dm_tran_locksAS TLON WT.resource_address=TL.lock_owner_address WHERE WT.wait_duration_ms>5000   AND WT.session_id> 50;

查询结果：

wait_seesion_id	wait_session_info	DatabaseName	wait_duration_ms	request_mode	wait_query_text	resource_type		bloking_session_id	blocking_query_text
113	10.64.34.182/CHOP3R8CWAS01. NCxxx	MEDICALMGMT	216394140	S	SELECT [task_date] [task_code] [execute_time] [task_state] FROM [task_state] WHERE [task_date]=@1 AND [task_code]=@2	PAGE	LCK_M_S	102	(@1 varchar(8000) @2 varchar(8000))SELECT [task_date] [task_code] [execute_time]
317	10.64.37.185/CNDCPLWAS02. NCxxx	MEDICALMGMT	417188390	S	SELECT [task_date] [task_code] [execute_time] [task_state] FROM [task_state] WHERE [task_date]=@1 AND [task_code]=@3	PAGE	LCK_M_S	102	(@1 varchar(8000) @2 varchar(8001))SELECT [task_date] [task_code] [execute_time]
149	10.64.34.81/CHOP3R8CWAS02. NCxxx	MEDICALMGMT	216377125	S	SELECT [task_date] [task_code] [execute_time] [task_state] FROM [task_state] WHERE [task_date]=@1 AND [task_code]=@4	PAGE	LCK_M_S	102	(@1 varchar(8000) @2 varchar(8002))SELECT [task_date] [task_code] [execute_time]
102	10.64.51.29/CTGP3APP01. NCxxx	MEDICALMGMT	995499094	S	SELECT [task_date] [task_code] [execute_time] [task_state] FROM [task_state] WHERE [task_date]=@1 AND [task_code]=@5	PAGE	LCK_M_S	160	select task_date task_code execute_time task_state from task_state where task_date='20131226' and task_code='ActiveNewPrice'