您现在的位置是:网站首页> 内容页

sql server 性能调优 资源等待之 LCk

  • 亿皇国际注册
  • 2019-08-26
  • 348人已阅读
简介 一. 概述  这次介绍实例级别资源等待LCK类型锁的等待时间,关于LCK锁的介绍可参考“sqlserver锁与事务拨云见日”。下面还是使用sys.dm_os_wait_sta

 一.  概述

  这次介绍实例级别资源等待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_msfrom sys.dm_os_wait_statswhere wait_type like "LCK%" order by wait_time_ms desc

 查出如下图所示:

   1.  分析介绍

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

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

      下面表格是统计分析

锁类型锁等待次数锁等待总时间(秒)平均每次等待时间(毫秒)最大等待时间
LCK_M_IX264565846.87122147623
LCK_M_U34725425.081126311
LCK_M_S613239.8993914938
LCK_M_X483277.878164684
LCK_M_SCH_S39777.8321966074
LCK_M_SCH_M11335.7833162268

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

 

2. 重现锁等待时间

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

 

-- 会话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秒,如下图:

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

  总结:可以看出资源等待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秒 以及谁阻塞了它的会话IDSELECT 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+")")

 

, 1, 0, 9);

文章评论

Top