SQLSERVER排查阻塞Word格式.docx
《SQLSERVER排查阻塞Word格式.docx》由会员分享，可在线阅读，更多相关《SQLSERVER排查阻塞Word格式.docx（16页珍藏版）》请在冰豆网上搜索。
我们很容易推断出问题的原因是由于BLOCKING导致的，但是我们无法得知BLOCKINGHEADER是什么，我们必须要等下次问题重现时，辅之以工具，才能得知BLOCKINGHEADER在做什么事情。
如果信息抓取时机不对，我们可能要等问题发生好几次，才能抓到。
这时候，客户和经理就会有抱怨了。
因为我们的系统是生产系统，问题每发生一次，都会对客户带来损失。
2011-06-0116:
22:
30.98spid1931
AlertThereare158Activedatabasesessionswhichistoohigh.
23:
31.16spid3248
AlertThereare342Activedatabasesessionswhichistoohigh.
24:
31.06spid3884
AlertThereare517Activedatabasesessionswhichistoohigh.
25:
31.08spid3688
AlertThereare694Activedatabasesessionswhichistoohigh.
26:
50.93Server
Using'
dbghelp.dll'
version'
4.0.5'
50.97Server
**Dumpthread-spid=0,EC=0x0000000000000000
***StackDumpbeingsenttoD:
\MSSQL10.INSTANCE\MSSQL\LOG\SQLDump0004.txt
********************************************************************************
*
*BEGINSTACKDUMP:
*
06/01/1116:
50spid4124
*DeadlockedSchedulers
*-------------------------------------------------------------------------------
*ShortStackDump
51.01Server
StackSignatureforthedumpis0x0000000000000258
BLOCKING的信息抓取有很多种方法。
这里罗列了几种。
并且对每种分析它的优缺点。
以便我们选择。
在枚举方法之前，我们先简单演示一下BLOCKING.
我们首先创建一个测试表:
DROPTABLE[TESTTABLE]
GO
CREATETABLE[dbo].[TESTTABLE]（
[ID][int]NULL,
[NAME][nvarchar]（50）NULL
）
INSERTINTOTESTTABLEVALUES（1,'
aaaa'
然后打开一个查询窗口，执行下面的语句,该语句修改一行数据，并等待3分钟，然后在结束transaction
BEGINTRANSACTION
UPDATETESTTABLESET[NAME]='
bbbb'
WHERE[ID]=1
WAITFORDELAY'
00:
03:
00'
COMMITTRANSACTION
这时候，如果打开另外一个查询窗口，执行下面的语句，下面的语句就会被BLOCK住。
cccc'
方法一,抓取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
*********lockinfor***********'
selectconvert（smalli
升级会员 