笔记66-徐 从等待状态判断系统资源瓶颈 P341

笔记66-徐 从等待状态判断系统资源瓶颈 P341

--从等待状态判断系统资源瓶颈 P341

--系统资源瓶颈包括：内存，磁盘I/O，CPU，锁资源阻塞

--sys.sysprocesses
--在SQL2005和SQL2008里增加了三张DMV可以得到更详细的信息

--sys.dm_exec_requests
--返回有关在SQL中执行的每个请求的信息，包括当前的等待状态


--sys.dm_exec_sessions
--对于SQL中每个经过身份验证的会话都返回相应的一行。
--sys.dm_exec_sessions是服务器范围的视图，显示了有关
--所有活动用户连接和内部任务的信息。此信息包含客户端版本，
--客户端程序名称，客户端登录时间，登录用户和当前会话设置等
--使用sys.dm_exec_sessions 首先可以查看当前的系统负荷标识
--相关会话，然后可以通过其他动态管理视图或动态管理函数了解
--有关该会话的详细信息


--sys.dm_exec_connections
--返回与SQL实例建立的连接有关的信息以及每个连接的详细信息

--关于这三张DMV的详细介绍，可用参阅SQL联机丛书

--需要说明的是sys.sysprocesses的结构是向前兼容的，他不支持多个活动结果集（MARS）
--这个概念。在SQL2000的时候，一个连接同时只能运行一个批处理指令。也就是说
--一个连接在一个时间只能有一个请求在运行。但是SQL2005以后，如果使用了
--MARS技术，一个连接可以同时开启多个结果集。而sys.sysprocesses视图不能
--正确显示这种行为。如果您的SQL已经升级到2005或以上，建议转用到这
--三张DMV


--在sys.sysprocesses和sys.dm_exec_requests里，都能看到当前会话状态，他们
--的常用查询可以是：

--对sysprocesses
SELECT
[spid],
[kpid],
[blocked],
[waittype],
[waittime],
[lastwaittype],
[waitresource],
[dbid],
[uid],
[cpu],
[physical_io],
[memusage],
[login_time],
[last_batch],
[open_tran],
[status],
[host_name],
[program_name],
[hostprocess],
[cmd],
[net_library],
[LoginName]
FROM sys.[sysprocesses]
WHERE [spid]>50



--对sys.dm_exec_requests
SELECT
s.[session_id],
s.[status],
s.[login_time],
s.[host_name],
s.[program_name],
s.[host_process_id],
s.[client_version],
s.[client_interface_name],
s.[login_name],
s.[last_request_start_time],
s.[last_request_end_time],
c.[connect_time],
c.[net_transport],
c.[net_packet_size],
c.[client_net_address],
r.[request_id],
r.[start_time],
r.[status],
r.[command],
r.[database_id],
r.[user_id],
r.[blocking_session_id],
r.[wait_type],
r.[wait_time],
r.[last_wait_type],
r.[wait_resource],
r.[open_transaction_count],
r.[transaction_id],
r.[percent_complete],
r.[cpu_time],
r.[reads],
r.[writes],
r.[granted_query_memory]
FROM sys.[dm_exec_requests] r
RIGHT OUTER JOIN sys.[dm_exec_sessions] s
ON r.[session_id]=s.[session_id]
RIGHT OUTER JOIN sys.[dm_exec_connections] c
ON s.[session_id]=c.[session_id]
WHERE s.[session_id]>50





--另外还有一个动态管理视图sys.dm_os_wait_stats，可以返回从SQL启动以来所有
--等待状态总的等待数和等待时间。从这些累积值，可以看出SQL经常会遇到哪一类等待

--[dm_os_wait_stats]字段定义
--wait_type:等待类型的名称
--waiting_tasks_count:该等待类型的等待数，该计数器在每开始一个等待时便会增加
--wait_time_ms:该等待类型的总等待时间（毫秒），该时间包括：signal_wait_time_ms
--max_wait_time_ms:该等待类型的最长等待时间
--signal_wait_time_ms:正在等待的线程从收到信号通知到其开始运行之间的时差

--不同的等待状态有不同的含义，代表着不同的系统资源。SQL是一个很复杂的系统，在
--SQL2005里，有不下200种等待状态（SQL2005有202种）。但是幸运的是，经常会引起问题的等待不是太多
--DBA无须去研究每一个等待状态


SELECT * FROM sys.[dm_os_wait_stats]


--------------------------------------等待状态的类型-------------------------------------------


--LCK_xx类型
--如果SQL经常有阻塞发生，会经常看到如下这些类似以“LCK_”开头的等待状态：
--LCK_M_BU:正在等待获取大容量更新锁BU
--LCK_M_IS:正在等待获取意向共享锁IS锁
--LCK_M_IU：正在等待获取意向更新锁IU
--LCK_M_IX:正在等待获取意向排他锁IX
--LCK_M_RIn_NL:正在等待获取当前键值上的NULL锁锁以及当前键和上一个键之间的插入范围锁
--键上的NULL锁是指立即释放的锁

--LCK_M_RIn_S:正在等待获取当前键值上的共享锁以及当前键和上一个键之间的插入范围锁
--LCK_M_RIn_U:正在等待获取当前键值上的更新锁以及当前键和上一个键之间的插入范围锁
--LCK_M_RIn_X:正在等待获取当前键值上的排他锁以及当前键和上一个键之间的插入范围锁
--LCK_M_RS_S:正在等待获取当前键上的共享锁以及当前键和上一个键之间的共享范围锁
--LCK_M_RS_U:正在等待获取当前键值上的更新锁以及当前键和上一个键之间的共享范围锁
--LCK_M_RX_S:正在等待获取当前键值上的共享锁以及当前键和上一个键之间的排他范围锁
--LCK_M_RX_U:正在等待获取当值键值上的更新锁以及当前键和上一个键之间的排他范围锁
--LCK_M_RX_X:正在等待获取当前键值上的排他锁以及当前键和上一个键之间的排他范围锁
--LCK_M_S:正在等待获取共享锁
--LCK_M_SCH_M:正在等待获取架构修改锁
--LCK_M_SCH_S:正在等待获取架构共享锁
--LCK_M_SIU:正在等待获取意向共享更新锁
--LCK_M_SIX:正在等待获取共享意向排他锁
--LCK_M_U:正在等待获取更新锁
--LCK_M_UIX:正在等待获取更新意向排他锁
--LCK_M_X:正在等待获取排他锁


--阻塞是SQL里最容易出现的等待状态


---------------------------------------PAGEIOLATCH_x-------------------------------------------------------


--pageiolatch_x与writelog
--当缓存在内存缓冲区域里的数据页面，和磁盘上数据文件里的数据页面进行交互时，为了保证
--不会有多个用户同时读取/修改内存里的数据页面，SQL会像对待表格里的数据一样，对内存中
--的页面实行加锁机制，以同步多用户并发处理。不同的是，在这里，SQL加的是latch（轻量级的锁）
--而不是lock


--例如，当SQL将数据页面从数据文件里读入内存时，为了防止其他用户对内存里的同一个数据
--页面进行访问，SQL会在内存的数据页面上加一个排他的latch。而当有任务要读缓存在内存
--里的页面时，会申请一个共享的latch。像LOCK一样，latch也会出现阻塞的现象。根据不同
--的等待资源，在SQL里等待的状态会是：

--PAGEIOLATCH_DT:destroy buffer page io latch
--PAGEIOLATCH_EX:exclusive buffer page io latch
--PAGEIOLATCH_KP:keep buffer page io latch
--PAGEIOLATCH_NL:null buffer page io latch
--PAGEIOLATCH_SH:shared buffer page io latch
--PAGEIOLATCH_UP:update buffer page io latch

--这里举一个最容易发生的等待：PAGEIOLATCH_SH 来做例子，看看这种等待是怎麽发生的

--有一个用户请求，必须读取整张X表，由worker x执行
--worker x 在执行表扫描的过程中发现他要读取数据页面1:100
--SQL发现页面1:100并不在内存中的数据缓存里
--SQL在缓冲池里找到一个页面的空间，在上面申请一个EX的latch，以防止数据从磁盘
--里读出来之前，有别人也来读取或修改这个页面

--worker x发起一个异步IO请求，要求从数据文件里读出页面1:100
--由于是个异步IO，worker x可以接着做他下面要做的事情。而下面要做的就是要读出内存中
--的页面1:100。读取的动作需要申请一个SH的latch

--由于worker x之前已经申请了一个EX latch还没释放，所以这个SH latch将被阻塞住。worker x
--被自己阻塞住了，等待的资源就是PAGEIOLATCH_SH

--当异步IO结束后，系统会通知worker x，你要的数据已经写入内存了

--这时候EX latch就被释放。接着worker x得到了他申请的SH latch

--数据页1:100 终于被worker x读到，读取工作结束，worker x可以继续下面的操作了

--由此可以看到，在发生PAGEIOLATCH类型的等待时，SQL是在等待某个IO动作的完成。
--所以如果一个SQL经常出现这一类的等待，说明磁盘的速度不能满足SQL的需要，
--他已经成为了SQL的一个瓶颈




--要强调的是，PAGEIOLATCH_x类型等待最常见的是两大类，PAGEIOLATCH_SH和
--PAGEIOLATCH_EX。PAGEIOLATCH_SH经常发生在用户想要去访问一个数据页面
--，而同时SQL却要把这个页面从磁盘读进内存。如果这个页面是用户经常有
--可能访问到的，那么说到底，问题是因为内存不够大，没能够将数据页面
--始终缓存在内存里。所以，往往是先有内存压力，触发SQL做了很多读取页面的工作
--，才引发磁盘读的瓶颈。这里磁盘瓶颈常常是内存瓶颈的副产品


--而PAGEIOLATCH_EX常常发生在用户对数据页面做了修改，SQL要向磁盘回写的时候，
--基本意味着磁盘的写入速度明显跟不上。这里和内存瓶颈没有直接关系


--和磁盘有关的另一个等待状态是WRITELOG，说明任务当前正在等待将日志记录写入日志文件
--出现这个等待状态，也意味着磁盘的写入速度明显跟不上


--和磁盘的写入速度有关的：PAGEIOLATCH_EX 和WRITELOG


--------------------------PAGELATCH_x-----------------------------------------------------------

--pagelatch_x类型是SQL在缓冲池里的数据页面上经常加的另一类latch
--注意：pagelatch_x和pageiolatch_x是完全不一样的。因为长得像，
--所以经常会被搞混。
--PAGEIOLATCH_X出现在SQL要和磁盘进行交互的时候，所以中间有IO这两个字


--PAGELATCH_X的作用：
--先重复一下，每一行数据在数据页面里是怎麽存放的

--每个SQL的数据页面大致分成3个部分：页头，页尾偏移量和数据存储部分。假设现在有一个表
--结构是：
CREATE TABLE test(
a INT,
b INT
)

--他在1:100这个页面上存储数据。那么这个页面结构大致如图
--在页头部分，会记录页面属性，包括页面编号等，还会记录当前页面空闲部分
--的起始位置在哪里

--这样SQL在要插入新数据的时候，就能够很快地找到开始插入的位置，而页尾的偏移量
--记录了每一条数据行的起始位置。这样SQL在找每一条记录的时候，就能很快找到
--不会把前一条记录和后一条搞混。在图例这一页里现在有两条记录，（1,100）和（2,200）
--第一条记录的开始位置是96，第二条记录的开始位置是111，从126开始，是空闲的空间

--当页面里的数据行发生变化的时候，SQL不但要去修改数据本身，还要修改这些偏移量
--的值，以保证SQL能够继续准确地管理数据页面里的每一行


--现在假设有两个用户同时向这张表里插入数据，一个人插入（3,300），另一个人插入
--（4,400）。SQL会为这两个插入语句各申请一个页面上的IX锁，防止有人将整个页面
--改掉。两个IX锁是相互兼容的，所以这两条插入语句可以同时运行。在事务逻辑上
--这两条语句插入的是两条不相干的记录，所以不应该相互阻塞，这样处理是正确的


--但是到了物理存储这一层，问题就来了，这时候新的数据应该放在（2,200）后面，
--也就是说，偏移量是126。可是该放谁呢？总不能两个值并列放吧？要想办法解决这个
--冲突，一定要定义出一个先后顺序



--SQL为了解决这类问题，引入了另一类页面上的latch：PAGELATCH。当一个任务要修改
--页面时，他必须先申请一个EX的latch。只有得到这个latch，才能修改页面里的内容

--所以这里的两个插入任务，不仅申请了页面上的锁，还要申请页面上的排他latch。假设
--（3,300）这个插入任务先申请到了，那（4,400）这个任务就会被阻塞住。所以，（3,300）
--这条记录能够被先插入

--当（3,300）插入完成后，他申请的latch被释放。（4,400）就能得到latch资源，终于
--轮到他做。（4,400）被插在了（3,300）的后面。这样，两个插入都正确地完成了

--由于数据页的修改都是在内存中完成的，所以每次修改的时间都应该非常短，几乎可以
--忽略不计。而pagelatch只是在修改的过程中才会出现，所以pagelatch的生存周期应该
--也非常短。如果这个资源成为了SQL经常等待的资源，可以说明以下问题：

--（1）SQL没有明显的内存和磁盘瓶颈（恭喜您！）

--（2）应用程序发来大量的并发语句在修改同一张表格里的记录，而表格架构设计以及
--用户业务逻辑使得这些修改都集中在同一个页面，或者数量不多的几个页面上。这些
--页面有时候也被称为 hot page 热力页。这样的瓶颈通常只会发生在并发用户比较多
--的，典型的OLTP系统上


--如果有兴趣的话，可以做下面的实验，来体会一下HOT PAGE问题是什么样子：
--（1）设置SQL 的最大线程数为350，以提高SQL的并发量（这里只是做实验，在
--生产环境下这麽做可得做测试，以验证没有负面影响）

EXEC [sys].[sp_configure] @configname = 'max worker threads', -- varchar(35)
    @configvalue = 350 -- int

--（2）创建一个测试数据库TestDB。在里面创建一张以Identity为属性的列做聚集索引的表
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable
(
  col1 INT IDENTITY(1,1) NOT NULL,
  col2 VARCHAR(50) NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX Idx1 ON [dbo].[TestTable]([col1])
GO


--col1列的属性是IDENTITY(1,1),决定了每一条插入的数据值，都会严格地以一递增。
--上面又有一个聚集索引，数据行将按照这一列的值排序存放。这两个条件加在一起
--，就意味着在一个时间点，只要页面当时还有空间，所有并发用户新插入的数据都
--将被严格地先后插入在同一个页面里。



--（3）用OSTRESS压力测试工具，模拟400个并发用户，每个用户要连续插入2000条记录


--（4）这时候再去检查DMV SELECT * FROM sys.[dm_exec_requests]，就可以看到PAGELATCH的等待状态了。

--在现实环境里，可以试想下面情形。一个股票交易系统，每一笔交易都会有一个流水号
--是递增而且不可重复的。而客户发过来的交易请求，都要存储在同一张交易表里。每一个
--新的交易，都要插入一条新记录。如果设计者选择在流水号上建聚集索引（这也是很自然的）
--就容易遇到HOT PAGE的PAGE LATCH资源瓶颈。在同一时间，只能有一个用户插入一笔交易


--怎样才能缓解这种瓶颈呢？
--（1）最简单的方法：是换一个数据列建聚集索引，而不要建立在IDENTITY字段上。这样表格
--里的数据就按照其他方式排序，同一时间的插入就有机会分散在不同的页面上

--（2）如果实在是一定要在IDENTITY的字段上建立聚集索引，建议根据其他某个数据列
--在表格上建立若干个分区（Partition）表分区，把一个表格分成若干个分区，可以
--使得接受新数据的页面数目增加

--还是以上面那个股票交易系统为例子。不同的股票属于不同的行业。开发者可以根据股票
--的行业属性，将一张交易表分成若干个分区。在SQL里，已分区表（Partition Table）
--的每个分区都是一个独立的存储单位。分属不同分区的数据行是严格分开存储的。所以
--同一个时间发生的交易记录，因其他行业不同，也会分别保存在不同的分区里。这样，
--在同一个时间点，可以插入不同行业的交易记录。每个分区上的HOT PAGE
--（接受新数据插入的PAGE）就不那么HOT了