首页 > 代码库 > 解决一阻塞语句CPU直降15%

解决一阻塞语句CPU直降15%

原本只是部署作业获取数据库中阻塞语句,中午测试汇集阻塞数据,发现某一服务器写入386行,而其他服务器只写入几行。
登录对应服务器查看详细信息,发现有四个时间点分别写入100来行记录
技术分享
对于第一行:会话183被会话221阻塞,阻塞时长1887ms,会话221持有18:1:4311755上的U锁,会话183等待18:1:4311755上的U锁。
查看BlockedBatch/BlockingBatch列,此处的阻塞与被阻塞对应的存储过程是相同的,只是传递的参数不同。存储过程定义如下

技术分享
CREATE PROCEDURE [dbo].[*LoadUserPersonalMsg]    @UserID INTASBEGIN    SET NOCOUNT ON;    SELECT ID, UserID, MsgType, MsgContent into #temp      FROM DBname.dbo.UserPersonalMsg with(nolock)     WHERE UserID = @UserID and getDate() > BeginTime and getDate() < EndTime        delete from DBname.dbo.UserPersonalMsg where ID in (select ID from #temp)END
View Code

BlockedStmt/BlockingStmt(未截图)显示阻塞发生在delete from DBname.dbo.UserPersonalMsg where ID in (select ID from #temp)上。
列BlockingHoldResource:pagelock fileid=1 pageid=4311755 dbid=18 id=lock1d73106c80 mode=U associatedObjectId=72057594060603392,查看页上的信息
技术分享
技术分享
阻塞语句已确定,持有/申请的资源也明确。分析语句,为什么会在那么多页上加U锁。查看UserPersonalMsg对象上的索引,只在Userid上有聚集索引,表的记录数16W,唯一Userid记录数15W+,大部分的Userid只有一条数据,极少数有两条。也就是每次delete的量只有1~2条,但删除的where条件使用的是自增列ID,导致聚集索引扫描。并且此存储过程执行比较频繁(约10次/秒)。之前有对此服务器优化过,当时集中在CPU>40且执行频繁的语句|过程。过滤之前的跟踪文件,只获取到13条此存储过程的语句,完美地错过了这个语句~
鉴于每个Userid返回的记录数很少,直接在ID上创建主键(表已存在聚集索引,对应会创建非聚集索引)。查看CPU使用情况
技术分享
2016-12-08 12:52分界点,从20%直接降到6%,惊呆了!!!
最开始获取的阻塞语句,DatabaseName有异常,当时是取sys.dm_exec_requests中的database_id,结果发现它和BlockingHoldResource中的dbid不一致,参考sys.dm_os_waiting_tasks 引发的疑问(上),修改为dm_tran_locks中的resource_database_id,测试把主键删除,查看等待,顺便说下此时CPU立马蹭到20%
技术分享
很简单的一个问题,关键是捕获并识别异常,优化是一个不断迭代的过程。

解决一阻塞语句CPU直降15%