SQLServer 2008数据库查看死锁、堵塞的SQL语句
查看那个表死锁
select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks where resource_type = 'OBJECT'
死锁和堵塞一直是性能测试执行中关注的重点。
下面是我整理的监控sql server数据库,在性能测试过程中是否出现死锁、堵塞的SQL语句,还算比较准备,留下来备用。
1 --每秒死锁数量 2 3 SELECT * 4 FROM sys.dm_os_performance_counters 5 WHERE counter_name LIKE 'Number of Deadlocksc%'; 6 7 --查询当前阻塞 8 9 WITH CTE_SID ( BSID, SID, sql_handle ) 10 AS ( SELECT blocking_session_id , 11 session_id , 12 sql_handle 13 FROM sys.dm_exec_requests 14 WHERE blocking_session_id <> 0 15 UNION ALL 16 SELECT A.blocking_session_id , 17 A.session_id , 18 A.sql_handle 19 FROM sys.dm_exec_requests A 20 JOIN CTE_SID B ON A.SESSION_ID = B.BSID 21 ) 22 SELECT C.BSID , 23 C.SID , 24 S.login_name , 25 S.host_name , 26 S.status , 27 S.cpu_time , 28 S.memory_usage , 29 S.last_request_start_time , 30 S.last_request_end_time , 31 S.logical_reads , 32 S.row_count , 33 q.text 34 FROM CTE_SID C 35 JOIN sys.dm_exec_sessions S ON C.sid = s.session_id 36 CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q 37 ORDER BY sid
在压力测试过程中,不间断的按F5键执行上面的SQL语句,如果出现死锁或者堵塞现象,就会在执行结果中罗列出来。如果每次连续执行SQL,都有死锁或者堵塞出现,说明死锁或者堵塞的比较严重。