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
升级会员 