太阳集团游戏官方网站 13

资源等待之,资源等待之PAGEIOLATCH

 一.  概述

  这次介绍实例级别资源等待LCK类型锁的等待时间,关于LCK锁的介绍可参考
“sql server
锁与事务拨云见日”。下面还是使用sys.dm_os_wait_stats
来查看,并找出耗时最高的LOK锁。

select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'LCK%' 
order by  wait_time_ms desc

 查出如下图所示:

太阳集团游戏官方网站 1

   1.  分析介绍

   重点介绍几个耗时最高的锁含义:

    LCK_M_IX:
正在等待获取意向排它锁。在增删改查中都会有涉及到意向排它锁。
  LCK_M_U: 正在等待获取更新锁。 在修改删除都会有涉及到更新锁。
  LCK_M_S:正在等待获取共享锁。
主要是查询,修改删除也都会有涉及到共享锁。
  LCK_M_X:正在等待获取排它锁。在增删改中都会有涉及到排它锁。
  LCK_M_SCH_S:正在等待获取架构共享锁。防止其它用户修改如表结构。
  LCK_M_SCH_M:正在等待获取架构修改锁 如添加列或删除列
这个时候使用的架构修改锁。

      下面表格是统计分析

锁类型 锁等待次数 锁等待总时间(秒) 平均每次等待时间(毫秒) 最大等待时间
LCK_M_IX 26456 5846.871 221 47623
LCK_M_U 34725 425.081 12 6311
LCK_M_S 613 239.899 391 4938
LCK_M_X 4832 77.878 16 4684
LCK_M_SCH_S 397 77.832 196 6074
LCK_M_SCH_M 113 35.783 316 2268

  注意: wait_time_ms
时间里,该时间表包括了signal_wait_time_ms信号等待时间,也就是说wait_time_ms不仅包括了申请锁需要的等待时间,还包括了线程Runnable
的信号等待。通过这个结论也能得出max_wait_time_ms
最大等待时间不仅仅只是锁申请需要的等待时间。

 

2. 重现锁等待时间

--  重置
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);  

 太阳集团游戏官方网站 2

--  会话1 更新SID=92525000, 未提交
begin tran 
update [dbo].[PUB_StockTestbak] set model='mmtest' where sid=92525000

-- 会话2 查询该ID, 由于会话1更新未提交 占用x锁,这里查询将阻塞
select * from [PUB_StockTestbak] where sid=92525000

   手动取消会话2的查询,占用时间是61秒,如下图:

太阳集团游戏官方网站 3

  再来统计资源等待LCK,如下图 :

太阳集团游戏官方网站 4

  总结:可以看出资源等待LCK的统计信息还是非常正确的。所以找出性能消耗最高的锁类型,去优化是很有必要。比较有针对性的解决阻塞问题。

3. 造成等待的现象和原因

现象:

  (1)  用户并发越问越多,性能越来越差。应用程序运行很慢。

  (2)  客户端经常收到错误 error 1222 已超过了锁请求超时时段。

  (3)  客户端经常收到错误 error 1205 死锁。

  (4)  某些特定的sql 不能及时返回应用端。

原因:

  (1) 用户并发访问越多,阻塞就会越来越多。

  (2) 没有合理使用索引,锁申请的数量多。

  (3) 共享锁没有使用nolock, 查询带来阻塞。 好处是必免脏读。

  (4) 处理的数据过大。比如:一次更新上千条,且并发多。

  (5) 没有选择合适的事务隔离级别,复杂的事务处理等。

4.  优化锁的等待时间

   在优化锁等待优化方面,有很多切入点 像前几篇中有介绍
CPU和I/O的耗时排查和处理方案。 我们也可以自己写sql来监听锁等待的sql
语句。能够知道哪个库,哪个表,哪条语句发生了阻塞等待,是谁阻塞了它,阻塞的时间。

  从上面的平均每次等待时间(毫秒),最大等待时间
作为参考可以设置一个阀值。 通过sys.sysprocesses 提供的信息来统计,
关于sys.sysprocesses使用可参考”sql server 性能调优
从用户会话状态分析”。
通过该视图
监听一段时间内的阻塞信息。可以设置每10秒跑一次监听语句,把阻塞与被阻塞存储下来。

   思想如下:

-- 例如 找出被阻塞会话ID 如时间上是2秒 以及谁阻塞了它的会话ID
SELECT spid,blocked #monitorlock FROM sys.sysprocesses 
where blocked>0 and    waittime>2000 

-- 通过while或游标来一行行获取临时表的 会话ID,阻塞ID,通过exec动态执行来获取sql语句文本 进行存储
exec('DBCC INPUTBUFFER('+@spid+')') 

exec('DBCC INPUTBUFFER('+@blocked+')') 

 

在今天的文章里,我想谈下在线索引重建操作( Online Index Rebuild
operations)
,它们在SQL Server
2014里有怎样的提升。我们都知道,自SQL Server
2005开始引入了在线索引重建操作。但这些在线操作并非真正的在线操作,因为在操作开始时,SQL
Server需要获得共享表锁(Shared Table Lock
(S) ),在操作结束时需要在对应表上获得架构修改锁(Schema Modification
Lock (Sch-M) )。因此这些操作是真正的在线操作,只是营销技巧(marketing
trick)。但是,亲,“在线”肯定比“部分在线”好听多了。

一.概念

  在介绍资源等待PAGEIOLATCH之前,先来了解下从实例级别来分析的各种资源等待的dmv视图sys.dm_os_wait_stats。它是返回执行的线程所遇到的所有等待的相关信息,该视图是从一个实际级别来分析的各种等待,它包括200多种类型的等待,需要关注的包括PageIoLatch(磁盘I/O读写的等待时间),LCK_xx(锁的等待时间),WriteLog(日志写入等待),PageLatch(页上闩锁)Cxpacket(并行等待)等以及其它资源等待排前的。 

  1.  下面根据总耗时排序来观察,这里分析的等待的wait_type 不包括以下

SELECT  wait_type ,
        waiting_tasks_count,
        signal_wait_time_ms ,
        wait_time_ms,
        max_wait_time_ms
FROM    sys.dm_os_wait_stats
WHERE   wait_time_ms > 0
        AND wait_type NOT IN ( 'CLR_SEMAPHORE', 'CLR_AUTO_EVENT',
                               'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
                               'SLEEP_TASK', 'SLEEP_SYSTEMTASK',
                               'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
                               'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
                               'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                               'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
                               'CLR_MANUAL_EVENT',
                               'DISPATCHER_QUEUE_SEMAPHORE',
                               'FT_IFTS_SCHEDULER_IDLE_WAIT',
                               'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN',
                               'SQLTRACE_INCREMENTAL_FLUSH_SLEEP' )
ORDER BY signal_wait_time_ms DESC

  下图排名在前的资源等待是重点需要去关注分析:

太阳集团游戏官方网站 5

  通过上面的查询就能找到PAGEIOLATCH_x类型的资源等待,由于是实例级别的统计,想要获得有意义数据,就需要查看感兴趣的时间间隔。如果要间隔来分析,不需要重启服务,可通过以下命令来重置

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);  

  wait_type:等待类型
  waiting_tasks_count:该等待类型的等待数
  wait_time_ms:该等待类型的总等待时间(包括一个进程悬挂状态(Suspend)和可运行状态(Runnable)花费的总时间)
  max_wait_time_ms:该等待类型的最长等待时间
  signal_wait_time_ms:正在等待的线程从收到信号通知到其开始运行之间的时差(一个进程可运行状态(Runnable)花费的总时间)
  io等待时间==wait_time_ms – signal_wait_time_太阳集团游戏官方网站,ms

尽管如此,SQL Server
2014还是在在线索引重建的开始和结束发生的阻塞做了一些改进。因此,在你执行在线索引重建时,你可以定义所谓的锁优先级(Lock Priority)。来看看下面的代码,你会看到起作用的新语法: 

二. PAGEIOLATCH_x

  2.1 什么是Latch

    在sql
server里latch是轻量级锁,不同于lock。latch是用来同步sqlserver的内部对象(同步资源访问),而lock是用来对于用户对象包括(表,行,索引等)进行同步,简单概括:Latch用来保护SQL server内部的一些资源(如page)的物理访问,可以认为是一个同步对象。而lock则强调逻辑访问。比如一个table,就是个逻辑上的概念。关于lock锁这块在”sql server
锁与事务拨云见日”中有详细说明。

  2.2 什么是PageIOLatch 

  当查询的数据页如果在Buffer
pool里找到了,则没有任何等待。否则就会发出一个异步io操作,将页面读入到buffer
pool,没做完之前,连接会保持在PageIoLatch_ex(写)或PageIoLatch_sh(读)的等待状态,是Buffer
pool与磁盘之间的等待。它反映了查询磁盘i/o读写的等待时间。
  当sql
server将数据页面从数据文件里读入内存时,为了防止其他用户对内存里的同一个数据页面进行访问,sql
server会在内存的数据页同上加一个排它锁latch,而当任务要读取缓存在内存里的页面时,会申请一个共享锁,像是lock一样,latch也会出现阻塞,根据不同的等待资源,等待状态有如下:PAGEIOLATCH_DT,PAGEIOLATCH_EX,PAGEIOLATCH_KP,PAGEIOLATCH_SH,PAGEIOLATCH_UP。重点关注PAGEIOLATCH_EX(写入)和PAGEIOLATCH_SH(读取)二种等待。

2.1  AGEIOLATCH流程图

  有时我们分析当前活动用户状态下时,一个有趣的现象是,有时候你发现某个SPID被自己阻塞住了(通过sys.sysprocesses了查看)
为什么会自己等待自己呢? 这个得从SQL server读取页的过程说起。SQL
server从磁盘读取一个page的过程如下:

太阳集团游戏官方网站 6

太阳集团游戏官方网站 7

  (1):由一个用户请求,获取扫描X表,由Worker x去执行。

  (2):在扫描过程中找到了它需要的数据页同1:100。

  (3):发面页面1:100并不在内存中的数据缓存里。

  (4):sql
server在缓冲池里找到一个可以存放的页面空间,在上面加EX的LATCH锁,防止数据从磁盘里读出来之前,别人也来读取或修改这个页面。

  (5):worker x发起一个异步i/o请求,要求从数据文件里读出页面1:100。

  (6):由于是异步i/o(可以理解为一个task子线程),worker
x可以接着做它下面要做的事情,就是读出内存中的页面1:100,读取的动作需要申请一个sh的latch。

  (7):由于worker
x之前申请了一个EX的LATCH锁还没有释放,所以这个sh的latch将被阻塞住,worker
x被自己阻塞住了,等待的资源就是PAGEIOLATCH_SH。

  最后当异步i/o结束后,系统会通知worker
x,你要的数据已经写入内存了。接着EX的LATCH锁释放,worker
x申请得到了sh的latch锁。

总结:首先说worker是一个执行单元,下面有多个task关联Worker上,
task是运行的最小任务单元,可以这么理解worker产生了第一个x的task任务,再第5步发起一个异步i/o请求是第二个task任务。二个task属于一个worker,worker
x被自己阻塞住了。 关于任务调度了解查看sql server
任务调度与CPU。

 2.2 具体分析

  通过上面了解到如果磁盘的速度不能满足sql
server的需要,它就会成为一个瓶颈,通常PAGEIOLATCH_SH
从磁盘读数据到内存,如果内存不够大,当有内存压力时候它会释放掉缓存数据,数据页就不会在内存的数据缓存里,这样内存问题就导致了磁盘的瓶颈。PAGEIOLATCH_EX是写入数据,这一般是磁盘的写入速度明显跟不上,与内存没有直接关系。

下面是查询PAGEIOLATCH_x的资源等待时间:

select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%' 
order by wait_type

下面是查询出来的等待信息:

PageIOLatch_SH
总等待时间是(7166603.0-15891)/1000.0/60.0=119.17分钟,平均耗时是(7166603.0-15891)/297813.0=24.01毫秒,最大等待时间是3159秒。

PageIOLatch_EX 总等待时间是(3002776.0-5727)/1000.0/60.0=49.95分钟,   
平均耗时是(3002776.0-5727)/317143.0=9.45毫秒,最大等待时间是1915秒。

太阳集团游戏官方网站 8

关于I/O磁盘 sys.dm_io_virtual_file_stats 函数也做个参考

SELECT  
       MAX(io_stall_read_ms) AS read_ms,
         MAX(num_of_reads) AS read_count,
       MAX(io_stall_read_ms) / MAX(num_of_reads) AS 'Avg Read ms',
         MAX(io_stall_write_ms) AS write_ms,
        MAX(num_of_writes) AS write_count,
         MAX(io_stall_write_ms) /  MAX(num_of_writes) AS 'Avg Write ms'
FROM    sys.dm_io_virtual_file_stats(null, null)
WHERE   num_of_reads > 0 AND num_of_writes > 0 

太阳集团游戏官方网站 9

  总结:PageIOLatch_EX(写入)跟磁盘的写入速度有关系。PageIOLatch_SH(读取)跟内存中的数据缓存有关系。通过上面的sql统计查询,从等待的时间上看,并没有清晰的评估磁盘性能的标准,但可以做评估基准数据,定期重置,做性能分析。要确定磁盘的压力,还需要从windows系统性能监视器方面来分析。
关于内存原理查看”sql server
内存初探“磁盘查看”sql
server I/O硬盘交互” 。

 1 ALTER INDEX idx_Col1 ON Foo REBUILD
 2 WITH
 3 (
 4    ONLINE = ON
 5    (
 6       WAIT_AT_LOW_PRIORITY 
 7       (
 8          MAX_DURATION = 1, 
 9          ABORT_AFTER_WAIT = SELF
10       )
11    )
12 ) 
13 GO

当阻塞情况发生时,你可以用WAIT_AT_LOW_PRIORITY关键字定义如何处理。使用第1个属性MAX_DURATION指定你想要等待的时间——这里是分钟,不是秒!用ABORT_AFTER_WAIT属性你指定哪个会话需要被SQL
Server回滚。SELF意味着那个ALTER INDEX
REBUILD语句会回滚,当你指定BLOCKERS时,阻塞的会话会回滚。当然,当没有阻塞发生时,在线索引重建操作会立即执行。因此这里你只能配置当阻塞情况发生时要怎么处理。

好了,我们来实操下。我们新建一个数据库,一个简单的表和一个聚集索引。 

 1 -- Creates a new database
 2 CREATE DATABASE Test
 3 GO
 4 
 5 -- Use the database
 6 USE Test
 7 GO
 8 
 9 -- Create a simple table
10 CREATE TABLE Foo
11 (
12     Col1 INT IDENTITY(1, 1) NOT NULL,
13     Col2 INT NOT NULL,
14     Col3 INT NOT NULL
15 )
16 GO
17 
18 -- Create a unique Clustered Index on the table
19 CREATE UNIQUE CLUSTERED INDEX idx_Col1 ON Foo(Col1)
20 GO
21 
22 -- Insert a few test records
23 INSERT INTO Foo VALUES (1, 1), (2, 2), (3, 3)
24 GO

 为了触发阻塞,我在不同的会话开始一个新的事务,但不提交:

1 BEGIN TRANSACTION
2 
3 UPDATE Foo SET Col2 = 2
4 WHERE Col1 = 1

这意味着我们在需要修改的记录上获得排它锁(Exclusive Lock
(X))
,在对应的页上获得意向排它锁(Intent-Exclusive Lock
(IX))
,在表本身获得意向排它锁(Intent-Exclusive Lock
(IX))
。我们刚刚在SQL Server里创建了典型的锁定层次(locking
hierarchy):表=>页=>记录。在表级别的意向排它锁(IX
Lock)和在线索引重建操作需要的共享锁(Shared
Lock)是不兼容的——典型的锁/阻塞情形发生了。当你现在执行在线索引重建操作时,会发生阻塞:

 

1 ALTER INDEX idx_Col1 ON Foo REBUILD
2 WITH
3 (
4    ONLINE = ON
5 )
6 GO

 

当你查看DMV sys.dm_tran_locks时,你会看到那个需要共享锁(Shared
Lock(S))的会话需要等待。这个会话会永远等待。我刚才就说过:“部分在线”……

1 SELECT * FROM    sys.dm_tran_locks

太阳集团游戏官方网站 10

当我们执行带有锁优先级(Lock
Priority)的在线索引重建时,有趣的事情发生了: 

 1 -- Perform an Online Index Rebuild
 2 ALTER INDEX idx_Col1 ON Foo REBUILD
 3 WITH
 4 (
 5    ONLINE = ON
 6    (
 7       WAIT_AT_LOW_PRIORITY 
 8       (
 9          MAX_DURATION = 1, 
10          ABORT_AFTER_WAIT = SELF
11       )
12    )
13 ) 
14 GO

太阳集团游戏官方网站 11

在这个情况下,我们的ALTER INDEX语句会等待1分钟(MAX_DURATION),然后语句本身取消了(ABORT_AFTER_WAIT)。

如果你在这里指定了BLOCKERS选项,那么阻塞的会话就会回滚。当我们同时(在1分钟期间)查看DMV sys.dm_tran_locks,我们看到了有趣的东西:

太阳集团游戏官方网站 12 

从图中可以看到,SQL
Server这里请求一个LOW_PRIORITY_WAIT的状态。因此3个请求状态(GRANT,WAIT,CONVERT)有了第4个选项:LOW_PRIORITY_WAIT。当我们查看DMV sys.dm_os_waiting_tasks时,事情变得有意思(59是执行语句的会话ID):

1 SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id='59'

太阳集团游戏官方网站 13

在线索引重建操作的等待会话报告了一个新的等待类型LCK_M_S_LOW_PRIORITY。这意味着当在线索引重建操作被阻塞时,我们可以从服务器级别(sys.dm_os_wait_stats)的等待统计信息里获得——不错!

但是LCK_M_S_LOW_PRIORITY并不是新的等待类型。在SQL
Server 2014里,当你查看DMV sys.dm_os_wait_stats时,会看到21个新的等待类型:

1 SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%LOW_PRIORITY%'
  • LCK_M_SCH_S_LOW_PRIORITY
  • LCK_M_SCH_M_LOW_PRIORITY
  • LCK_M_S_LOW_PRIORITY
  • LCK_M_U_LOW_PRIORITY
  • LCK_M_X_LOW_PRIORITY
  • LCK_M_IS_LOW_PRIORITY
  • LCK_M_IU_LOW_PRIORITY
  • LCK_M_IX_LOW_PRIORITY
  • LCK_M_SIU_LOW_PRIORITY
  • LCK_M_SIX_LOW_PRIORITY
  • LCK_M_UIX_LOW_PRIORITY
  • LCK_M_BU_LOW_PRIORITY
  • LCK_M_RS_S_LOW_PRIORITY
  • LCK_M_RS_U_LOW_PRIORITY
  • LCK_M_RIn_NL_LOW_PRIORITY
  • LCK_M_RIn_S_LOW_PRIORITY
  • LCK_M_RIn_U_LOW_PRIORITY
  • LCK_M_RIn_X_LOW_PRIORITY
  • LCK_M_RX_S_LOW_PRIORITY
  • LCK_M_RX_U_LOW_PRIORITY
  • LCK_M_RX_X_LOW_PRIORITY

所有主要的等待类型(LCK_M_*)都有额外的锁优先级等待类型。这个非常酷,也非常强大,因为你很容易从中可以跟踪到为什么在线重建索引操作被阻塞。另外,对于分区切换(Partition
Switching)也适用同样的技术(锁优先级(Lock
Priorities)),因为在切换期间,操作也要在2个表(原表,目标表)上获取架构修改锁(Schema
Modification Lock (Sch-M))。

我希望这篇文章可以让你理解SQL
Server 2014里的锁优先级(Lock Priorities),还有为什么SQL
Server里的“在线”操作实际上只是“部分在线”。

感谢关注!

参考文章:

https://www.sqlpassion.at/archive/2014/01/02/how-sql-server-2014-improves-online-operations-that-arent-online-operations/

发表评论

电子邮件地址不会被公开。 必填项已用*标注