SQL Server数据库阻塞，死锁，排查语句

sql 查询卡顿数据库

SELECT SPID=p.spid,
       DBName = convert(CHAR(20),d.name),
       ProgramName = program_name,
       LoginName = convert(CHAR(20),l.name),
       HostName = convert(CHAR(20),hostname),
       Status = p.status,
       BlockedBy = p.blocked,
       LoginTime = login_time,
       QUERY = CAST(TEXT AS VARCHAR(MAX))
FROM   MASTER.dbo.sysprocesses p
       INNER JOIN MASTER.dbo.sysdatabases d
         ON p.dbid = d.dbid
       INNER JOIN MASTER.dbo.syslogins l
         ON p.sid = l.sid
       CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE  p.blocked = 0
       AND EXISTS (SELECT 1
                   FROM   MASTER..sysprocesses p1
                   WHERE  p1.blocked = p.spid)

存储过程查询具体的死锁，阻塞中的sql语句

新建存储过程

create procedure [dbo].[sp_who_lock]
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR

-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end


GO

后续可直接执行存储过程查看

exec sp_who_lock

结束阻塞的进程

kill 进程号

正在执行中的sql

SELECT  [Spid] = session_Id, 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)

选取top 10 最耗cpu语句

SELECT TOP 10
cpu_time,
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50
ORDER BY [cpu_time] DESC
    

查询锁定的事务

 select spid 进程,STATUS 状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid),1,30)
,用户机器名称=SUBSTRING(hostname,1,12)
,是否被锁住=convert(char(3),blocked)
,数据库名称=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待类型
,last_batch 最后批处理时间,open_tran 未提交事务的数量
from master.sys.sysprocesses
Where  status='sleeping' and waittype=0x0000 and open_tran>0

查询死锁信息

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
    xdr.query('.') AS [Event_Data]
FROM (SELECT CAST([target_data] AS XML) AS Target_Data
            FROM sys.dm_xe_session_targets AS xt
            INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
            WHERE xs.name = N'system_health'
              AND xt.target_name = N'ring_buffer'
    ) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;

查询锁和进程spid情况

exec sp_lock;

DBCC INPUTBUFFER(136);

SELECT es.session_id, ib.event_info
FROM sys.dm_exec_sessions AS es
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
WHERE es.is_user_process = 1;

查询数据库中页的信息和页数据

select * from sys.dm_db_page_info( DatabaseId , FileId , PageId ,'DETAILED');

DBCC PAGE(DatabaseId, FileId ,PageId, 3);

dm_db_page_info查询page_type 列中的值对应不同类型的页面，常见的类型包括：
1：数据页（DATA_PAGE）
2：索引页（INDEX_PAGE）
3：LOB 数据页（LOB_DATA_PAGE）
4：差异备份位图（DIFF_MAP_PAGE）
5：清除备份位图（ML_MAP_PAGE）
10：堆数据页（HEAP_PAGE）