本文共 6202 字，大约阅读时间需要 20 分钟。
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 AdventureWorks2008R2GO 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_statusFROM sys.dm_tran_locks AS L1 JOIN sys.dm_tran_locks AS L2 ON L1.resource_associated_entity_id = L2.resource_associated_entity_idWHERE 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_textFROM sys.dm_os_waiting_tasks AS T JOIN sys.dm_tran_locks AS L ON T.resource_address = L.lock_owner_addressWHERE 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 MasterGOSELECT xed.value('@timestamp', 'datetime') AS Creation_Date , xed.query('.') AS Extend_EventFROM ( 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 BlockingTsqlFROM 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.nameFROM-sys.partitions AS p·INNER JoIN sys.indexes As i ON i.object_id=p.object_id AND i.index_id=p.index_idwHERE p.partition_id - 4395513238772318208，
转载地址：http://msesn.baihongyu.com/