笔记101 查询阻塞与死锁的sql语句

笔记101 查询阻塞与死锁的sql语句

--查询阻塞与死锁的sql语句
--1、
--排在前两位的等待状态有下面几个：asynch_io_,completion,io_completion,logmgr,writelog,pageiolatch_x
--这些等待状态意味着有I/O等待
--如果排在前两位的等待状态以这样开头:LCK_M_?? 说明系统经常有阻塞
SELECT TOP 2 [wait_type] FROM sys.[dm_os_wait_stats] ORDER BY [wait_time_ms] DESC


--2、阻塞发生频率
EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname 要查询阻塞的数据库
SELECT * FROM sys.[dm_db_index_operational_stats](10,NULL,NULL,null) --根据上面的dbid填入第一个参数

--3、开启阻塞事件报告
EXEC [sys].[sp_configure] @configname = 'blocked process threshold', -- varchar(35)
    @configvalue = 1 -- int
    RECONFIGURE

--4、平均阻塞时间 建议阀值>100ms
EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname 要查询阻塞的数据库
SELECT [row_lock_wait_in_ms],[page_lock_wait_in_ms],[page_latch_wait_in_ms],[page_io_latch_wait_in_ms] FROM sys.[dm_db_index_operational_stats](10,NULL,NULL,null) --根据上面的dbid填入第一个参数

--5、查询当前数据库上所有用户表格在row lock上发生阻塞的频率
--查询当前数据库上所有用户表在row lock上发生的阻塞频率
use GPOSDB   --要查询阻塞的数据库
DECLARE @dbid INT
SELECT @dbid=DB_ID()
SELECT
dbid=database_id,
objectname=OBJECT_NAME(s.object_id),
indexname=i.name,
i.index_id,
partition_number,
row_lock_count,
row_lock_wait_count,
[block%]=CAST(100*row_lock_wait_count/(1+row_lock_count)AS NUMERIC(15,2)),
row_lock_wait_in_ms,
[avg row lock waits in ms]=CAST(1*row_lock_wait_in_ms/(1+row_lock_wait_count)AS NUMERIC(15,2))
FROM sys.dm_db_index_operational_stats(@dbid,NULL,NULL,null) AS s,
sys.indexes AS i
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable')=1
AND i.object_id=s.object_id
AND i.index_id=s.index_id
ORDER BY row_lock_wait_count DESC



--6、查看DMV看一下数据库中有多少个锁-----------------------------------------------------------
USE [AdventureWorks] --要查询申请锁的数据库
GO
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID('AdventureWorks')  ----要查询申请锁的数据库
ORDER BY [request_session_id],[resource_type]

 

 

---

 

使用扩展事件xevent记录历史发生的阻塞

 https://mp.weixin.qq.com/s/qZepNFWhqTy0Obqf56u5NQ

 

设置阻塞报警时间 blocked process threshold是sql2005新增的一个选项

超过这个阻塞阈值都会记录到扩展事件里面

sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 20 ;   --设置为20秒
GO  
RECONFIGURE ;  
GO  

 

 

 

 

查找历史发生的阻塞

笔者见过很多人遇到长时间阻塞导致应用响应缓慢时候，病急乱投医，指望一个SQLServer高手来了，马上就能问题抓出来。

排查是否是阻塞导致的问题，首先要排查下面的问题：

1. 是否是服务器CPU高导致

2. 在服务器上执行一个SQL是否缓慢。

3. 网络正常。

如果以上几个都正常，那多半是阻塞问题导致。

SQLServer默认是不捕获历史的长时间阻塞的问题的，这里需要新建一个SQLServer的扩展事件。

第一步命名，

第二步选择不使用模板，

第三步搜索block，选择blocked_process_report

 

 

 

然后选择几个最重要的指标，sql_text, session_id

 

 

下一步，注意在最后一步选择将数据保存到文件。点击完成。

 

 

这时我们在扩展事件里面可以看到block_report已经创建好了。

 

 

 

最后不要忘记设置阻塞捕获的时间的阈值。数据库默认是不会启用阻塞捕获，所以一定要启用设置这个阈值。

下面的示例将 blocked process threshold 设置为 20 秒，超过该阈值将为阻塞的每个任务生成阻塞的进程报告。

sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 20 ;  
GO  
RECONFIGURE ;  
GO  

 

好了，我们再此运行之前的SQL语句（不在赘述），然后再去查看block_report会发生什么。

 

打开报告，扩展事件文件(xx.xel )需用Python进行分析，因为是xml格式文件，需用用xml模块 来分析和格式化然后在前端显示

 

 

f