SQLSERVER排查阻塞.docx
- 文档编号:1122254
- 上传时间:2022-10-17
- 格式:DOCX
- 页数:16
- 大小:278.57KB
SQLSERVER排查阻塞.docx
《SQLSERVER排查阻塞.docx》由会员分享,可在线阅读,更多相关《SQLSERVER排查阻塞.docx(16页珍藏版)》请在冰豆网上搜索。
SQLSERVER排查阻塞
SQLServer允许并发操作,BLOCKING是指在某一操作没有完成之前,其他操作必须等待,以便于保证数据的完整性。
BLOCKING的解决方法要查看BLOCKING的头是什么,为什么BLOCKING头上的语句执行的很慢。
通常来讲只要我们能找到BLOCKING头上的语句,我们总能够想出各种各种的办法,来提升性能,缓解或解决BLOCKING的问题。
但是问题的关键是,我们不知道BLOCKING什么时候会发生。
用户跟我们抱怨数据库性能很差,等我们连上数据库去查看的时候,那时候有可能BLOCKING可能就已经过去了。
性能又变好了。
或者由于问题的紧急性,我们直接重新启动服务器以恢复运营。
但是问题并没有最终解决,我们不知道下次问题会在什么时候发生。
BLOCKING问题的后果比较严重。
因为终端用户能直接体验到。
他们提交一个订单的时候,无论如何提交不上去,通常几秒之内能完成的一个订单提交,甚至要等待十几分钟,才能提交完成。
更有甚者,极严重的BLOCKING能导致SQLServer停止工作。
如下面的SQLERRORLOG所表示,在短短的几分钟之内,SPID数据从158增长到694,并马上导致SQLServer打了一个dump,停止工作。
我们很容易推断出问题的原因是由于BLOCKING导致的,但是我们无法得知BLOCKINGHEADER是什么,我们必须要等下次问题重现时,辅之以工具,才能得知BLOCKINGHEADER在做什么事情。
如果信息抓取时机不对,我们可能要等问题发生好几次,才能抓到。
这时候,客户和经理就会有抱怨了。
因为我们的系统是生产系统,问题每发生一次,都会对客户带来损失。
2011-06-0116:
22:
30.98spid1931 AlertThereare158Activedatabasesessionswhichistoohigh.
2011-06-0116:
23:
31.16spid3248 AlertThereare342Activedatabasesessionswhichistoohigh.
2011-06-0116:
24:
31.06spid3884 AlertThereare517Activedatabasesessionswhichistoohigh.
2011-06-0116:
25:
31.08spid3688 AlertThereare694Activedatabasesessionswhichistoohigh.
2011-06-0116:
26:
50.93Server Using'dbghelp.dll'version'4.0.5'
2011-06-0116:
26:
50.97Server **Dumpthread-spid=0,EC=0x0000000000000000
2011-06-0116:
26:
50.97Server ***StackDumpbeingsenttoD:
\MSSQL10.INSTANCE\MSSQL\LOG\SQLDump0004.txt
2011-06-0116:
26:
50.97Server ********************************************************************************
2011-06-0116:
26:
50.97Server *
2011-06-0116:
26:
50.97Server *BEGINSTACKDUMP:
2011-06-0116:
26:
50.97Server * 06/01/1116:
26:
50spid4124
2011-06-0116:
26:
50.97Server *
2011-06-0116:
26:
50.97Server *DeadlockedSchedulers
2011-06-0116:
26:
50.97Server *
2011-06-0116:
26:
50.97Server ********************************************************************************
2011-06-0116:
26:
50.97Server *-------------------------------------------------------------------------------
2011-06-0116:
26:
50.97Server *ShortStackDump
2011-06-0116:
26:
51.01Server StackSignatureforthedumpis0x0000000000000258
BLOCKING的信息抓取有很多种方法。
这里罗列了几种。
并且对每种分析它的优缺点。
以便我们选择。
在枚举方法之前,我们先简单演示一下BLOCKING.
我们首先创建一个测试表:
DROPTABLE[TESTTABLE]
GO
CREATETABLE[dbo].[TESTTABLE](
[ID][int]NULL,
[NAME][nvarchar](50)NULL
)
GO
INSERTINTOTESTTABLEVALUES(1,'aaaa')
GO
然后打开一个查询窗口,执行下面的语句,该语句修改一行数据,并等待3分钟,然后在结束transaction
BEGINTRANSACTION
UPDATETESTTABLESET[NAME]='bbbb'WHERE[ID]=1
WAITFORDELAY'00:
03:
00'
COMMITTRANSACTION
这时候,如果打开另外一个查询窗口,执行下面的语句,下面的语句就会被BLOCK住。
UPDATETESTTABLESET[NAME]='cccc'WHERE[ID]=1
方法一,抓取SQLProfiler
======================
SQLProfiler里面包含大量的信息。
其中有一个事件在ErrorsandWarnings->BlockedProcessReport专门用来获得blocking的情况。
但是因为信息量比较大,而且我们并不能很好的估算在什么时候会产生blocking,另外在生产环境使用Profiler,对性能可能会有影响,所以SQLProfiler并不是最合适的工具。
我们在这里并不对它赘述。
方法二,执行查询
================
如果我们检查问题的时候,blocking还存在,那么,我们可以直接可以运行几个查询,得知BLOCKINGHEADER的信息
SELECT*FROMsys.sysprocesseswherespid>50
上述查询只是告诉我们,BLOCKINGHEADER的头是SPID=53,但是并没有告诉我们SPID=53在做什么事情。
我们可以用下面的查询,得到SPID=53的信息
DBCCINPUTBUFFER(53)
我们可以把上述的两个查询合并起来,用下面的查询:
SELECTSPID=p.spid,
DBName=convert(CHAR(20),d.name),
ProgramName=program_name,
LoginName=convert(CHAR(20),l.name),
HostName=convert(CHAR(20),hostname),
Status=p.status,
BlockedBy=p.blocked,
LoginTime=login_time,
QUERY=CAST(TEXTASVARCHAR(MAX))
FROMMASTER.dbo.sysprocessesp
INNERJOINMASTER.dbo.sysdatabasesd
ONp.dbid=d.dbid
INNERJOINMASTER.dbo.sysloginsl
ONp.sid=l.sid
CROSSAPPLYsys.dm_exec_sql_text(sql_handle)
WHEREp.blocked=0
ANDEXISTS(SELECT1
FROMMASTER..sysprocessesp1
WHEREp1.blocked=p.spid)
这样,一次执行,就能告诉我们BLOCKINGheader的SPID信息,以及该SPID在做的语句。
我们可以进一步研究该语句,以理解为什么该语句执行很慢。
用这个方法有一个缺点,就是使用的时候,要求BLOCKING是存在的。
如果BLOCKING已经消失了,那么我们的方法就不管用了。
方法三,长期执行一个BLOCKINGSCRIPT
==================================
因为我们通常无法知道BLOCKING什么时候会产生,所以通常的办法是我们长期运行一个BLOCKINGSCRIPT,这样,等下次发生的时候,我们就会有足够的信息。
长期运行BLOCKINGSCRIPT对性能基本上是没有影响的。
因为我们每隔10秒钟抓取一次信息。
缺点是,如果问题一个月才发生一次,那么,我们的BLOCKING日志信息会很大。
所以这种方法适用于几天之内能重现问题。
运行方法如下:
如果要停止运行,我们按ctrl+c就可以了。
BLOCKING的信息存在log.out这个文件中
我们可以打开log.out这个文件,会发现SPID54被SPID53给Block住了。
而随后,我们可以看到SPID=53在做什么事情:
下面是BLOCKINGSCRIPT的脚本,我们可以把它存为blocking.sql
usemaster
go
while1=1
begin
print'Starttime:
'+convert(varchar(26),getdate(),121)
Print'Runningprocesses'
selectspid,blocked,waittype,waittime,lastwaittype,waitresource,dbid,uid,cpu,physical_io,memusage,login_time,last_batch,
open_tran,status,hostname,program_name,cmd,net_library,loginame
fromsysprocesses
--where(kpid<>0)or(spid<51)
--Changeitifyouonlywanttoseetheworkingprocesses
print'*********lockinfor***********'
selectconvert(smalli
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLSERVER 排查 阻塞