SQLSERVER 查询 query 执行状况

一些背景知识:

@@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}