SQL Server 查杀死锁

 

 


SELECT request_session_id AS spid ,
OBJECT_NAME(resource_associated_entity_id) AS 'table'
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';


KILL [spid];

 

 

复制代码
--查看当前的数据库⽤户连接
USE master
GO
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50


--选取前10个最耗CPU时间的会话
SELECT TOP 10[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 AND DB_NAME(der.[database_id])='DBID'
ORDER BY [cpu_time] DESC


--查询前10个最耗CPU时间的SQL语句
--在SSMS⾥选择以⽂本格式显⽰结果
SELECT TOP 10
dest.[text] AS 'sql语句'
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

--查询CPU占⽤⾼的语句
SELECT TOP 10   total_worker_time/execution_count AS avg_cpu_cost,
plan_handle,   execution_count,   (SELECT SUBSTRING(text,
statement_start_offset/2 + 1,      (CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
复制代码

 

 

复制代码
--数据库进程堵塞达到400以上意味着数据库即将卡顿,可用下面的sql辅助分析问题

--1.查询数据库阻塞与死锁的进程清单（可分析清单详情，看看死锁id对应的执行sql，分析sql语句，杀掉select语句的进程）
SELECT  'kill', request_session_id spid, OBJECT_NAME( resource_associated_entity_id )
tableName FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT'
--and OBJECT_NAME( resource_associated_entity_id )='TableName'
order by request_session_id


--2.查询锁表及次数（可分析根据表名汇总的详情，看看死锁最多的表执行的sql）
select  resource_associated_entity_id,OBJECT_NAME(resource_associated_entity_id),count(*) tableName 
from sys.dm_tran_locks where resource_type='OBJECT'  
and  resource_associated_entity_id  is not null group by resource_associated_entity_id order by tableName desc ;

--3.查询数据库中当前进程阻塞情况
select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执行的操作
from master..sysprocesses a,master..sysprocesses b
where a.blocked<>0 and a.blocked= b.spid

--4.查看系统内所有的活动进程 BLK不为0的为死锁
exec sp_who 'active'

--5.查询数据库中死锁及阻塞的详细信息（需sa用户权限）
exec sp_who_lock

--6.查询进程ID执行语句
DBCC INPUTBUFFER (78)

--7.查询前10个耗性能的SQL语句
SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(max), text)) * 2
         ELSE statement_end_offset
      END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC
复制代码

 

 

查询阻塞

复制代码
declare @spid int,@bl int,@waittime int,
 @intTransactionCountOnEntry  int,
        @intRowcount    int,
        @intCountProperties   int,
        @intCounter    int,
        @ip    varchar(30)
 create table #tmp_lock_who (
 id int identity(1,1),
 spid smallint,
 bl smallint,
 waittime int,
 ip varchar(30))
 
 
 insert into #tmp_lock_who(spid,bl,waittime,ip) 
 select  0 ,blocked,waittime,CLIENT_NET_ADDRESS ip
   from (select * from sysprocesses where  blocked>0 ) a 
   left join sys.dm_exec_connections C 
    on spid = C.SESSION_ID 
   where not exists(select * from (select * from sysprocesses where  blocked>0 ) b 
   where a.blocked=spid)
   union select spid,blocked,waittime,CLIENT_NET_ADDRESS ip from sysprocesses
   left join sys.dm_exec_connections C 
    on spid = C.SESSION_ID  where  blocked>0

  
-- 找到临时表的记录数
 select  @intCountProperties = Count(*),@intCounter = 1
 from #tmp_lock_who
 
 
 if @intCountProperties=0
  select '现在没有阻塞和死锁信息' as message

-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
  select  @spid = spid,@bl = bl,@waittime=waittime,@ip=ip
  from #tmp_lock_who where Id = @intCounter 
 begin
  if @spid =0 
            select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,IP:'+@ip+',等待时长:'+ CAST(@waittime AS VARCHAR(100))  +'毫秒,其执行的SQL语法如下'
 else
            select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,IP:'+@ip+',,等待时长:'+ CAST(@waittime AS VARCHAR(100))  +'毫秒,其当前进程执行的SQL语法如下'
 DBCC INPUTBUFFER (@bl )
 end 

-- 循环指针下移
 set @intCounter = @intCounter + 1
end

drop table #tmp_lock_who
复制代码

 

兴扬复制

复制代码
select '死锁' '类型',OBJECT_NAME( resource_associated_entity_id) '表名', spid '进程id',blocked '因此id被堵塞',hostname '主机名',CLIENT_NET_ADDRESS ip,waittime '等待时间(毫秒)'
from (select * from sysprocesses where blocked>0 ) a 
left join sys.dm_exec_connections C 
on spid = C.SESSION_ID 
left join sys.dm_tran_locks d
on spid = request_session_id
where not exists(select * from (select * from sysprocesses where blocked>0 ) b 
where a.blocked=spid) and resource_associated_entity_id<=2147483647
union 
select '堵塞' '类型',OBJECT_NAME( resource_associated_entity_id) '表名',spid '进程id',blocked '因此id被堵塞',hostname '主机名',CLIENT_NET_ADDRESS ip,waittime '等待时间(毫秒)' from sysprocesses 
left join sys.dm_exec_connections C 
on spid = C.SESSION_ID 
left join sys.dm_tran_locks d
on spid = request_session_id
where blocked>0 and resource_associated_entity_id<=2147483647
order by '进程id'


--查询数据库阻塞与死锁的进程清单（可分析清单详情，看看死锁id对应的执行sql）
SELECT request_session_id spid, OBJECT_NAME( resource_associated_entity_id )
tableName FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT'
--and OBJECT_NAME( resource_associated_entity_id )='WS_CarEntryExitInfo'
order by request_session_id


--查询锁表及次数（可分析根据表名汇总的详情，看看死锁最多的表执行的sql）
select resource_associated_entity_id,OBJECT_NAME(resource_associated_entity_id),count(*) tableName 
from sys.dm_tran_locks where resource_type='OBJECT' 
and resource_associated_entity_id is not null group by resource_associated_entity_id order by tableName desc ;

--查询数据库中当前进程阻塞情况
select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执行的操作
from master..sysprocesses a,master..sysprocesses b
where a.blocked<>0 and a.blocked= b.spid




--查看系统内所有的活动进程 BLK不为0的为死锁
exec sp_who 'active'



--查询进程ID执行语句
DBCC INPUTBUFFER (62)

--【查看日志】(开始日期包含当天，结束日期不含当天）
exec xp_readerrorlog 0, 1, N'', N'', '2022-10-16', '2022-10-17', N'desc';

--查询前10个耗性能的SQL语句
SELECT TOP 10
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC


--查询数据表的总行数
select c.value TableName, t.name as TableCode,i.rows as [RowCount]
from sys.tables as t join sysindexes as i on t.object_id = i.id and i.indid <=1
left join sys.extended_properties c on c.major_id = t.object_id and minor_id=0
order by [RowCount] desc

--清除所有缓存
--DBCC DROPCLEANBUFFERS 


--查询未提交的事务
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

--查询耗性能的SQL语句
SELECT TOP 10
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC


--查询缺失的索引
SELECT TOP 10
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
复制代码

 

查询执行语句：

复制代码
SELECT TOP 10
    total_worker_time / execution_count AS [avg_cpu_cost],
    plan_handle,
    execution_count,
    sql_handle,
    (SELECT SUBSTRING(text, statement_start_offset / 2 + 1,
        (CASE 
             WHEN statement_end_offset = -1
             THEN LEN(CONVERT(nvarchar(max), text)) * 2
             ELSE statement_end_offset
         END - statement_start_offset) / 2)
     FROM sys.dm_exec_sql_text(sql_handle)
    ) AS [query_text]
FROM sys.dm_exec_query_stats
WHERE (
    SELECT SUBSTRING(text, statement_start_offset / 2 + 1,
        (CASE 
             WHEN statement_end_offset = -1
             THEN LEN(CONVERT(nvarchar(max), text)) * 2
             ELSE statement_end_offset
         END - statement_start_offset) / 2)
    FROM sys.dm_exec_sql_text(sql_handle)
) LIKE '%特定文本%'
ORDER BY [avg_cpu_cost] DESC;
复制代码

 

posted @   三瑞  阅读(344)  评论(0)  编辑  收藏  举报
相关博文：
·  SQL Server 查杀死锁
·  SQL SERVER 中 sys.dm_tran_locks 消息 297，级别 16，状态 1，第 6 行 用户没有执行此操作的权限。
·  sqlserver数据库死锁查询，删除，语句耗时查询
·  sql server 查询和Kill死锁进程
·  SQL Server 查询是否死锁与解决办法
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示
AI IDE Trae