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,'+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' 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!