笔记101 查询阻塞与死锁的sql语句
1 --查询阻塞与死锁的sql语句
2 --1、
3 --排在前两位的等待状态有下面几个：asynch_io_,completion,io_completion,logmgr,writelog,pageiolatch_x
4 --这些等待状态意味着有I/O等待
5 --如果排在前两位的等待状态以这样开头:LCK_M_?? 说明系统经常有阻塞
6 SELECT TOP 2 [wait_type] FROM sys.[dm_os_wait_stats] ORDER BY [wait_time_ms] DESC
7
8
9 --2、阻塞发生频率
10 EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname 要查询阻塞的数据库
11 SELECT * FROM sys.[dm_db_index_operational_stats](10,NULL,NULL,null) --根据上面的dbid填入第一个参数
12
13 --3、开启阻塞事件报告
14 EXEC [sys].[sp_configure] @configname = 'blocked process threshold', -- varchar(35)
15 @configvalue = 1 -- int
16 RECONFIGURE
17
18 --4、平均阻塞时间 建议阀值>100ms
19 EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname 要查询阻塞的数据库
20 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填入第一个参数
21
22 --5、查询当前数据库上所有用户表格在row lock上发生阻塞的频率
23 --查询当前数据库上所有用户表在row lock上发生的阻塞频率
24 use GPOSDB --要查询阻塞的数据库
25 DECLARE @dbid INT
26 SELECT @dbid=DB_ID()
27 SELECT
28 dbid=database_id,
29 objectname=OBJECT_NAME(s.object_id),
30 indexname=i.name,
31 i.index_id,
32 partition_number,
33 row_lock_count,
34 row_lock_wait_count,
35 [block%]=CAST(100*row_lock_wait_count/(1+row_lock_count)AS NUMERIC(15,2)),
36 row_lock_wait_in_ms,
37 [avg row lock waits in ms]=CAST(1*row_lock_wait_in_ms/(1+row_lock_wait_count)AS NUMERIC(15,2))
38 FROM sys.dm_db_index_operational_stats(@dbid,NULL,NULL,null) AS s,
39 sys.indexes AS i
40 WHERE OBJECTPROPERTY(s.object_id,'IsUserTable')=1
41 AND i.object_id=s.object_id
42 AND i.index_id=s.index_id
43 ORDER BY row_lock_wait_count DESC
44
45
46
47 --6、查看DMV看一下数据库中有多少个锁-----------------------------------------------------------
48 USE [AdventureWorks] --要查询申请锁的数据库
49 GO
50 SELECT
51 [request_session_id],
52 c.[program_name],
53 DB_NAME(c.[dbid]) AS dbname,
54 [resource_type],
55 [request_status],
56 [request_mode],
57 [resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
58 p.[index_id]
59 FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
60 ON a.[resource_associated_entity_id]=p.[hobt_id]
61 LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
62 WHERE c.[dbid]=DB_ID('AdventureWorks') ----要查询申请锁的数据库
63 ORDER BY [request_session_id],[resource_type]
