赞
踩
1侦测阻塞问题
阻塞会发生在很多情况下,它会引起终端用户的响应时间变长。而且由于锁住了数据,
可能会得不到想要的结果,甚至会产生连接超时等问题。
sQL Server提供了–些工具来侦测基于锁的阻塞,比如相关的DMVs、性能监视器、
SQLDiag等。下面来简要介绍一下。
.1) PerfMon counters为性能监视器,其中SQL Server:General Statistics事件中的
Processes blocked计数器,显示被阻塞进程的数量。还可以在SQL Server:Wait Statistics事
件的Lock Waits计数器中查看锁的数量和锁的持续时间。但是得到的只是概要信息。
2 ) DMVs:可以使用sys.dm_os_waiting_tasks来返回当前正在等待的任务信息。对于
这个DMV的说明,请查阅联机丛书。注意DMV 返回的是任务级别的信息,而不是会话级
别的,如果-个查询是并行运行,其中一个线程被阻塞,这个DMV只会显示这个线程的信
息。某些情况下,sys.dm_os_waiting_tasks中 blocking_session_id可能为null,因为没有正
在阻塞的会话,或者SQL Server无法标识这个会话。但是在以下情形下blocking_session_.
id会出现负数。
-2:被锁定的资源属于一个孤立的分布式事务。
…
-3:被锁定的资源属于–个延迟恢复事务。
.
-4:门锁等待。
下面是查看阻塞持续时间超过5000ms的会话。
SELECT W.session_id AS waiting_session_id ,
W.waiting_task_address ,
W.wait_duration_ms ,
W.wait_type ,
W.blocking_session_id ,
W.resource_description
FROM sys.dm_os_waiting_tasks AS W
WHERE W.wait_duration_ms > 5000
AND blocking_session_id IS NOT NULL;
也可以用下面的语句查看库内所有在WAIT状态的锁,并了解它们在等待什么锁。
USE AdventureWorks2008R2 GO SELECT L1.resource_type , DB_NAME(L1.resource_database_id) AS DatabaseName , CASE L1.resource_type WHEN 'OBJECT' THEN OBJECT_NAME(L1.resource_associated_entity_id, L1.resource_database_id) WHEN 'DATABASE' THEN 'DATABASE' ELSE CASE WHEN L1.resource_database_id = DB_ID() THEN ( SELECT OBJECT_NAME(object_id, L1.resource_database_id) FROM sys.partitions WHERE hobt_id = L1.resource_associated_entity_id ) ELSE NULL END END AS ObjectName , L1.resource_description , L1.request_session_id , L1.request_mode , L1.request_status FROM sys.dm_tran_locks AS L1 JOIN sys.dm_tran_locks AS L2 ON L1.resource_associated_entity_id = L2.resource_associated_entity_id WHERE L1.request_status <> L2.request_status AND ( L1.resource_description = L2.resource_description OR ( L1.resource_description IS NULL AND L2.resource_description IS NULL ) ) ORDER BY L1.resource_database_id , L1.resource_associated_entity_id , L1.request_status ASC;
返回阻塞的信息及相关语句。
SELECT T.session_id AS waiting_session_id , DB_NAME(L.resource_database_id) AS DatabaseName , T.wait_duration_ms / 60000. AS duration_in_minutes , T.waiting_task_address , L.request_mode , ( SELECT SUBSTRING(Q.text, ( R.statement_start_offset / 2 ) + 1, ( ( CASE R.statement_end_offset WHEN -1 THEN DATALENGTH(Q.text) ELSE R.statement_end_offset END - R.statement_start_offset ) / 2 ) + 1) FROM sys.dm_exec_requests AS R CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS Q WHERE R.session_id = L.request_session_id ) AS waiting_query_text , L.resource_type , L.resource_associated_entity_id , T.wait_type , T.blocking_session_id , T.resource_description AS blocking_resource_description , CASE WHEN T.blocking_session_id > 0 THEN ( SELECT ST2.text FROM sys.sysprocesses AS P CROSS APPLY sys.dm_exec_sql_text(P.sql_handle) AS ST2 WHERE P.spid = T.blocking_session_id ) ELSE NULL END AS blocking_query_text FROM sys.dm_os_waiting_tasks AS T JOIN sys.dm_tran_locks AS L ON T.resource_address = L.lock_owner_address WHERE T.wait_duration_ms > 5000 AND T.session_id > 50;
在 Extended Events中,默认的事件会话是system_health,用于收集 SQL Server发生
的错误,包括死锁信息。system_health会话使用-个名为ring_buffer的目标,来收集内存
中的事件信息,然后以XML格式存储在sys.dm_xe_session_targets 这个DMV中。另外可
以把这个DMV通过关联另外-个DMV: sys.dm_xe_sessions,获取相关信息。下面是查询
死锁信息的语句:
USE Master
GO
SELECT xed.value('@timestamp', 'datetime') AS Creation_Date ,
xed.query('.') AS Extend_Event
FROM ( SELECT CAST([target_data] AS XML) AS Target_Data
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
WHERE xs.name = N'system_health'
AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')
AS XEventData ( xed )
ORDER BY Creation_Date DESC
下面的DMV代码可以用来捕获阻塞信息。
SELECT dtl.request_session_id AS WaitingSessionID , der.blocking_session_id AS BlockingSessionID , dowt.resource_description , der.wait_type , dowt.wait_duration_ms , DB_NAME(dtl.resource_database_id) AS DatabaseName , dtl.resource_associated_entity_id AS WaitingAssociatedEntity , dtl.resource_type AS WaitingResourceType , dtl.request_type AS WaitingRequestType , dest.[text] AS WaitingTSql , dtlbl.request_type BlockingRequestType , destbl.[text] AS BlockingTsql FROM sys.dm_tran_locks AS dtl JOIN sys.dm_os_waiting_tasks AS dowt ON dtl.lock_owner_address = dowt.resource_address JOIN sys.dm_exec_requests AS der ON der.session_id = dtl.request_session_id CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest LEFT JOIN sys.dm_exec_requests derbl ON derbl.session_id = dowt.blocking_session_id OUTER APPLY sys.dm_exec_sql_text(derbl.sql_handle) AS destbl LEFT JOIN sys.dm_tran_locks AS dtlbl ON derbl.session_id = dtlbl.request_session_id;
其中resource_description列的值如下(不同的机器值会不-一样):
ridlock fileid=1 pageid=411 dbid=2 id=lock368aab480 mode=X associatedObject-
Id–4395513238772318208
对于dbid=2,通过SELECT Db_name(2)可以知道是在TempDB中运行。mode=-X,证明
加上了排他锁,因为已经在实际 UPDATE,所以已经从U锁升级到X锁。至于associatedObj
ectld=4395513238772318208.前而已经提讨,可用以下代码查看:
SELECT OBJECT_MAME(i.objectid),
.i.name
FROM-sys.partitions AS p
·
INNER JoIN sys.indexes As i ON i.object_id=p.object_id
AND i.index_id=p.index_id
wHERE p.partition_id - 4395513238772318208,
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。