一些背景知识:
@@SPID当前连接到数据库的session id
sys.dm_exec_requests
https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql
sys.dm_exec_sql_text(函数):
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sql-text-transact-sql
CXPACKET(一种比较常见的因为多线程并行执行引起的阻塞):
https://www.sqlshack.com/troubleshooting-the-cxpacket-wait-type-in-sql-server/
sp_who2
SELECT [Spid] = session_id ,
sp.ecid ,
[Database] = DB_NAME(sp.dbid) ,
[User] = nt_username ,
[Status] = er.status ,
[Wait] = wait_type ,
[Wait_Time] = wait_time ,
[Individual Query] = 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) ,
[Parent Query] = qt.text ,
Program = program_name ,
hostname ,
nt_domain ,
start_time,DATEADD(hour, DATEDIFF(hour,GETDATE(),GETUTCDATE())+8, start_time)
,er.sql_handle
,er.statement_start_offset
,er.statement_end_offset
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE session_id > 50 -- Ignore system spids.
AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.
ORDER BY 1 , 2
SELECT session_id ,status ,blocking_session_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';
kill {SPID}