MSSQL 死锁或阻塞检测

查找正在阻塞其他进程或被其他进程所阻塞的进程

sp_who或sp_who2

sp_lock

sys.sysprocesses

--blocked表示被该进程锁阻塞
SELECT * FROM sys.sysprocesses 
WHERE blocked > 0

sys.dm_tran_locks

SELECT   
        t1.resource_type,  
        t1.resource_database_id,  
        t1.resource_associated_entity_id,  
        t1.request_mode,  
        t1.request_session_id,  
        t2.blocking_session_id  
    FROM sys.dm_tran_locks as t1  
    INNER JOIN sys.dm_os_waiting_tasks as t2  
        ON t1.lock_owner_address = t2.resource_address;

sys.sm_os_wait_tasks

--blocking_session_id
select * from sys.dm_os_waiting_tasks

DMV常用视图

--这个语句可以查看执行时间,sql语句,主机等信息
SELECT
    [Spid] = session_Id,
	sp.blocked,
    ecid,
    [Database] = DB_NAME(sp.dbid),
    [User] = nt_username,
    [Status] = er.status,
    [Wait] = wait_type,
    [Individual Query] = SUBSTRING(
        qt.text,
        er.statement_start_offset / 2,
        (
            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
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)

捕获死锁或阻塞

sql server profile

SQLTRACE

--开启
dbcc tranceon(1204,-1)
dbcc tranceon(1222,-1)
--关闭
dbcc tranceoff(1204,-1)
dbcc tranceoff(1222,-1)

扩展事件(消耗资源小,推荐)