笔记101 查询阻塞与死锁的sql语句
笔记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]