运行缓慢的查询语句（阻塞）（二）--识别阻塞

来源：互联网发布：暴走大事件之网络语编辑：程序博客网时间：2024/06/14 07:33

这些天看了一篇微软官方发布的MS SQL Server2008性能问题处理及优化的英文文档，里面知识点介绍地很详细，在现实工作中也很实用，遂产生了想把它翻译一下的念头。翻译的过程，既可以帮助自己复习一下这些技术，也可以向其他还不熟悉这一块的朋友介绍一些新的知识，何乐而不为呢。只是这篇文章有点长，我会分成几篇随笔去介绍，所以，不光是对我耐性的考验，也是对你的考验哦！

-------------------------------------------

识别长的阻塞

正如之前提到的，阻塞在SQL Server中是很正常的并且只是逻辑锁为了维护事务一致性的外在表现。然而，当等待超出了阈值，它就会影响相应的速度。为了识别那些长时间运行的阻塞，你可以使用BlockedProcessThreshold配置参数去创建一个用户--配置的服务器端的阻塞阈值。这个阈值定义的单位是秒。任何超出这个阈值的阻塞都会触发一个会被SQL Trace追踪到的trace event。

例如，一个200秒的阻塞运行阈值可以使用下面的步骤在SSMS中配置：

1. 运行语句：sp_configure 'blocked process threshold',200

2. 过去的配置就会被重载

3. 阻塞运行阈值一旦被建立后，追踪trace event. 这样就会被SQL Trace或者SQL Server Profiler追踪到了。

4. 如果你正在使用SQL Trace，使用sp_trace_setevent和event_id=137

5. 如果你正在使用SQL Server Profiler，选择Blocked process report event类别（在Errors和Warnings对象下面）。请看截图1.

截图1：捕获长阻塞和死锁

注意这是一个轻量级的捕获，因为仅仅追踪了一个阻塞查过了阈值，或者一个死锁发生的情况。对于每一个200秒钟的间隙，当某个锁被阻塞了，一个捕获事务就会被激发。这也就意味着说一个单独的锁如果超过了600秒钟，那它就会导致生成三个捕获事件。看截图2.

截图2：Reporting Blocking>阻塞阈值

捕获事件包含整个的SQL 语句，既包括引起阻塞的语句，也包含被阻塞的语句。在当前这个例子里，UPDATE Customers语句阻塞了SELECT From Customers语句。

使用sys.dm_db_index_operational_stats查看每一对象的阻塞情况

DMV sys.dm_db_index_operational_stats提供了完整的索引使用情况统计，包括阻塞。从阻塞层面来看，它提供了详细的在每个表，每个分区上的锁数量的统计信息。比如说包含对于某个给定表或者索引的历史信息的访问，locks(row_lock_count),blocks(process_virtual_memory_low),以及waits(row_lock_wait_in_ms)。

从这个DMV中可以获取到的有用信息类型包括：

持有的锁的数量，例如，行或者页.
阻塞或者等待的数量，例如，行或者页.
阻塞或者等待的时间，例如，行或者页.
页闩等待数量的等待。page_latch_wait的等待时间：这个包含在某个特定页上竞争，比如说，递增的主键插入。在这种情况下，热点是最后一页，所以多个写入者往相同的最后一页中同时尝试加上页闩.这个会被显示为Pagelatch waits。
page_io_latch_wait等待时间：当用户需要的某个页不再缓冲池中时，一个I/O闩就会发生.一个缓慢的或者超负荷的I/O子系统可能有些时候会遇到PageIOlatch等待，实际上I/O问题。这些问题会因缓存溢出或者缺失索引而引起。
页闩等待时间。

其它类型的信息也用来访问索引：

访问类型，例如，范围或者单一查找。
在叶级别的insert，update，以及delete。
比叶层级高的层级上的Insert,update以及delete。在叶层级之上的活动是索引维护。每个叶的第一行有一个上面层级的入口。如果一个新的页被安排在了叶上，上面的层级将会在叶的第一行上有一个新的入口。
在叶层级的页合并。这些被释放页之前的显示结果是空的，因为它们里面的行都已经被删除掉了。
索引维护。在比叶层级高的层级上合并的页都是空页，这些页会被释放掉，因为没有行在叶上被删除，这就导致了保留中间层的页是空的。每个叶上的第一行都有一个跟上面层级的接口。如果足够的行在叶层级被删除，中间层级的那些包含原始入口的第一行的索引页就会是空的。

这些信息是在实例启动以后就逐渐积累的。这些信息在实例被重启以后就不复存在了，并且没有任何位置去重新设定它。这个DMV返回过来的信息只有在元数据缓存对象对象中显示堆或者索引可用时才会存在。无论什么何时，当堆或者索引的元数据被带进去元数据缓存时，每列的数据会被设定为0。统计信息会被积累，直到缓存对象从元数据缓存中被移除。然而，你可以间隙性地到处这些数据到其它的表中，一直去你可以更深层次地进行查询。

使用等待纵览阻塞造成的性能影响

SQL Server 2008提供了超过100种等待类型去捕获应用程序性能。任何时间出现了用户连接的等待，SQL Server开始累积等待时间。比如，应用程序请求的资源，例如I/O，锁，或者内存并且可以等待资源变得可用。这种针对所有连接的等待信息被概要化并被类别化，以至于可以使用一个性能profile去获取已经给定的工作负载。这样的话，SQL等待类别识别并且分类来自于应用程序负载或者用户透视图的用户等待。

这个查询列举出了SQL Server中的10个top等待。这些等待是被累积计算的但是你可以使用DBCC SQLPERF（[sys.dm_os_wait_stats],clear）去重新设定它们。

select top 10 *from sys.dm_os_wait_stats order by wait_time_ms desc

下面是查询输出结果。一些需要注意的关键点有：

一些等待是正常的，例如有些由后台进程导致的，例如lazy writer引发的等待。
一些会话为了获取一个SH锁等待了很长时间。
The signal wait is the time between when a worker has been granted access to the resource and the time it gets scheduled on the CPU. A long signal wait may imply high CPU contention.一个显著的等待：一个工作者已经被授权了访问资源及它被指定使用CPU的时间之间的间隔。一个较长的显著等待往往预示着存在CPU竞争。

wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms

------------------ -------------------- -------------------- -----------------------------

LAZYWRITER_SLEEP 415088 415048437 1812 156

SQLTRACE_BUFFER_FLUSH 103762 415044000 4000 0

LCK_M_S 6 25016812 23240921 0

WRITELOG 7413 86843 187 406

LOGMGR_RESERVE_APPEND 82 82000 1000 0

SLEEP_BPOOL_FLUSH 4948 28687 31 15

LCK_M_X 1 20000 20000 0

PAGEIOLATCH_SH 871 11718 140 15

PAGEIOLATCH_UP 755 9484 187 0

IO_COMPLETION 636 7031 203 0

为了分析等待状况，你需要间歇性地查询这些数据然后分析它。

会话-级别的等待统计

为了识别会话-级别或者语句-级别的等待统计，在过去的SQL Server版本中不太可能，Extend Event工具只是一个想象中的工作。现在可以在整个运行阶段伸缩性捕获等待统计信息。

下面这个实例Extended Events 会话捕获所有的等待，包括SQL Server内部及外部的，比如id为54的会话。

-- sqlserver.session_id is the ID of the target session you want to trace. I am using 54 in the example. Replace it accordingly-- make sure C:\xevent folder existscreate event session session_waits on serveradd event sqlos.wait_info    (action (sqlserver.sql_text, sqlserver.plan_handle, sqlserver.tsql_stack)WHERE sqlserver.session_id=54 and duration>0), add event sqlos.wait_info_external    (action (sqlserver.sql_text, sqlserver.plan_handle, sqlserver.tsql_stack)WHERE sqlserver.session_id=54 and duration>0)add target package0.asynchronous_file_target      (SET filename=N'C:\xevent\wait_stats.xel', metadatafile=N'C:\xevent\wait_stats.xem');alter event session session_waits on server state = start;go-- wait for monitored workload in target session (54 in this example) to finish.

为了从输出文档中读取结果值，运行下面的查询：

alter event session session_waits on server state = stopdrop event session session_waits on serverselect CONVERT(xml, event_data).value('(/event/data/text)[1]','nvarchar(50)') as 'wait_type',CONVERT(xml, event_data).value('(/event/data/value)[3]','int') as 'duration',CONVERT(xml, event_data).value('(/event/data/value)[6]','int') as 'signal_duration'into #eventdatafrom sys.fn_xe_file_target_read_file(N'C:\xevent\wait_stats*.xel', N'C:\xevent\wait_stats*.xem', null, null)-- save to temp table, #eventdataselect wait_type, SUM(duration) as 'total_duration', SUM(signal_duration) as 'total_signal_duration'from #eventdata group by wait_typedrop table #eventdata go

实例输出如下：

wait_type total_duration total_signal_duration

-------------------------------------------------- -------------- ---------------------

NETWORK_IO 233 0

PREEMPTIVE_OS_WAITFORSINGLEOBJECT 231 576

WAITFOR 7000 0

PAGEIOLATCH_UP 624 0

PAGELATCH_UP 2320 45

PAGELATCH_SH 45 10

WRITELOG 30 0