SQLSERVER排查阻塞Word格式文档下载.docx

SQLSERVER排查阻塞Word格式文档下载.docx

《SQLSERVER排查阻塞Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《SQLSERVER排查阻塞Word格式文档下载.docx（16页珍藏版）》请在冰豆网上搜索。

BLOCKING问题的后果比较严重。

因为终端用户能直接体验到。

他们提交一个订单的时候，无论如何提交不上去，通常几秒之内能完成的一个订单提交，甚至要等待十几分钟，才能提交完成。

更有甚者，极严重的BLOCKING能导致SQLServer停止工作。

如下面的SQLERRORLOG所表示,在短短的几分钟之内，SPID数据从158增长到694,并马上导致SQLServer打了一个dump,停止工作。

我们很容易推断出问题的原因是由于BLOCKING导致的，但是我们无法得知BLOCKINGHEADER是什么，我们必须要等下次问题重现时，辅之以工具，才能得知BLOCKINGHEADER在做什么事情。

如果信息抓取时机不对，我们可能要等问题发生好几次，才能抓到。

这时候，客户和经理就会有抱怨了。

因为我们的系统是生产系统，问题每发生一次，都会对客户带来损失。

2011-06-0116:

22:

30.98spid1931AlertThereare158Activedatabasesessionswhichistoohigh.2011-06-0116:

23:

31.16spid3248AlertThereare342Activedatabasesessionswhichistoohigh.2011-06-0116:

24:

31.06spid3884AlertThereare517Activedatabasesessionswhichistoohigh.2011-06-0116:

25:

31.08spid3688AlertThereare694Activedatabasesessionswhichistoohigh.2011-06-0116:

26:

50.93ServerUsingdbghelp.dllversion4.0.52011-06-0116:

50.97Server*Dumpthread-spid=0,EC=0x00000000000000002011-06-0116:

50.97Server*StackDumpbeingsenttoD:

MSSQL10.INSTANCEMSSQLLOGSQLDump0004.txt2011-06-0116:

50.97Server*2011-06-0116:

50.97Server*BEGINSTACKDUMP:

50.97Server*06/01/1116:

50spid41242011-06-0116:

50.97Server*DeadlockedSchedulers2011-06-0116:

50.97Server*-2011-06-0116:

50.97Server*ShortStackDump2011-06-0116:

51.01ServerStackSignatureforthedumpis0x0000000000000258BLOCKING的信息抓取有很多种方法。

这里罗列了几种。

并且对每种分析它的优缺点。

以便我们选择。

在枚举方法之前，我们先简单演示一下BLOCKING.我们首先创建一个测试表:

DROPTABLETESTTABLEGOCREATETABLEdbo.TESTTABLE（IDintNULL,NAMEnvarchar（50）NULL）GOINSERTINTOTESTTABLEVALUES（1,aaaa）GO然后打开一个查询窗口，执行下面的语句,该语句修改一行数据，并等待3分钟，然后在结束transactionBEGINTRANSACTIONUPDATETESTTABLESETNAME=bbbbWHEREID=1WAITFORDELAY00:

03:

00COMMITTRANSACTION这时候，如果打开另外一个查询窗口，执行下面的语句，下面的语句就会被BLOCK住。

UPDATETESTTABLESETNAME=ccccWHEREID=1方法一,抓取SQLProfiler=SQLProfiler里面包含大量的信息。

其中有一个事件在ErrorsandWarnings-BlockedProcessReport专门用来获得blocking的情况。

但是因为信息量比较大，而且我们并不能很好的估算在什么时候会产生blocking，另外在生产环境使用Profiler,对性能可能会有影响，所以SQLProfiler并不是最合适的工具。

我们在这里并不对它赘述。

方法二,执行查询=如果我们检查问题的时候，blocking还存在，那么，我们可以直接可以运行几个查询，得知BLOCKINGHEADER的信息SELECT*FROMsys.sysprocesseswherespid50上述查询只是告诉我们，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.sysprocessespINNERJOINMASTER.dbo.sysdatabasesdONp.dbid=d.dbidINNERJOINMASTER.dbo.sysloginslONp.sid=l.sidCROSSAPPLYsys.dm_exec_sql_text（sql_handle）WHEREp.blocked=0ANDEXISTS（SELECT1FROMMASTER.sysprocessesp1WHEREp1.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.sqlusemastergowhile1=1beginprintStarttime:

+convert（varchar（26）,getdate（）,121）PrintRunningprocessesselectspid,blocked,waittype,waittime,lastwaittype,waitresource,dbid,uid,cpu,physical_io,memusage,login_time,last_batch,open_tran,status,hostname,program_name,cmd,net_library,loginamefromsysprocesses-where（kpid0）or（spid50openibufferfetchnextfromibufferintospidwhile（fetch_status!

=-1）beginprintprintDBCCINPUTBUFFERFORSPID+spidexec（dbccinputbuffer（+spid+）fetchnextfromibufferintospidenddeallocateibufferwaitfordelay0:

0:

10End这种方法的缺陷就是，log.out会比较巨大，会占用很大的空间，如果blocking一个月甚至更长时间才发生一次，那我们的这个方法就不太适宜。

方法四,我们用AgentJob来检查BLOCKING=长期运行一个BLOCKINGSCRIPT的缺点是我们每隔一段时间，去查询信息，但是大多数收集的信息是无用的。

所以会导致日志文件巨大，对于一个生产系统来讲，磁盘空间满可不是个好事情，另外，有一些客户对于用命令行来长期运行TSQL脚本有所顾忌，所以我们做了一个改进。

这次，我们只收集有用的信息。

对于无用的信息我们不关注。

这样能极大减少日志大小。

我们首先创建一个观察数据库，然后建立两张表格Blocking_sysprocesses和Blocking_SQLText,建立一个存储过程和一个Job,该Job每隔一段时间去调用存储过程。

只有发现有blocking的，我们才记录到表格Blocking_sysprocesses和Blocking_SQLText这两个表格中。

如果跟blocking无关，我们就不对它进行记录。

下面是TSQL语句:

CREATEDATABASEMonitorBlockingGOUSEMonitorBlockingGOCREATETABLEBlocking_sysprocesses（spidsmallint,kpidsmallint,blockedsmallint,waitTypebinary

（2）,waitTimebigInt,lastWaitTypenchar（32）,waitResourcenchar（256）,dbIDsmallint,uidsmallint,cpuint,physical_IOint,memusageint,login_Timedatetime,last_Batchdatetime,open_Transmallint,statusnchar（30）,sidbinary（86）,hostNamenchar（128）,program_Namenchar（128）,hostProcessnchar（10）,cmdnchar（16）,nt_Domainnchar（128）,nt_UserNamenchar（128）,net_Librarynchar（12）,loginNamenchar（128）,context_Infobinary（128）,sqlHandlebinary（20）,CapturedTimeStampdatetime）GOCREATETABLEdbo.Blocking_SqlText（spidsmallint,sql_textnvarchar（2000）,Capture_Timestampdatetime）GOCREATEPROCEDUREdbo.checkBlockingASBEGINSETNOCOUNTON;

SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTEDdeclareDurationint-inmilliseconds,1000=1secdeclarenowdatetimedeclareProcessesintselectDuration=100-inmilliseconds,1000=1secselectProcesses=0selectnow=getdate（）CREATETABLE#Blocks_rg（spidsmallint,kpidsmallint,blockedsmallint,waitTypebinary

（2）,waitTimebigInt,lastWaitTypenchar（32）,waitResourcenchar（256）,dbIDsmallint,uidsmallint,cpuint,physical_IOint,memusageint,login_Timedatetime,last_Batchdatetime,open_Transmallint,statusnchar（30）,sidbinary（86）,hostNamenchar（128）,program_Namenchar（128）,hostProcessnchar（10）,cmdnchar（16）,nt_Domainnchar（128）,nt_UserNamenchar（128）,net_Librarynchar（12）,loginNamenchar（128）,context_Infobinary（128）,sqlHandlebinary（20）,CapturedTimeStampdatetime）INSERTINTO#Blocks_rgSELECTspid,kpid,blocked,waitType,waitTime,lastWaitType,waitResource,dbID,uid,cpu,physical_IO,memusage,login_Time,last_Batch,open_Tran,status,sid,hostName,program_name,hostProcess,cmd,nt_Domain,nt_UserName,net_Library,loginame,context_Info,sql_Handle,nowasCapture_TimestampFROMmaster.sysprocesseswhereblocked0ANDwaitTimeDurationSETProcesses=rowcountINSERTinto#Blocks_rgSELECTsrc.spid,src.kpid,src.blocked,src.waitType,src.waitTime,src.lastWaitType,src.waitResource,src.dbID,src.uid,src.cpu,src.physical_IO,src.memusage,src.login_Time,src.last_Batch,src.open_Tran,src.status,src.sid,src.hostName,src.program_name,src.hostProcess,src.cmd,src.nt_Domain,src.nt_UserName,_Library,src.loginame,src.context_Info,src.sql_Handle,nowasCapture_TimestampFROMmaster.sysprocessessrcinnerjoin#Blocks_rgtrgtontrgt.blocked=src.spidifProcesses0BEGININSERTdbo.Blocking_sysprocessesSELECT*from#Blocks_rgDECLARESQL_Handlebinary（20）,SPIDsmallInt;

DECLAREcur_handleCURSORFORSELECTsqlHandle,spidFROM#Blocks_rg;

OPENcur_HandleFETCHNEXTFROMcur_handleINTOSQL_Handle,SPIDWHILE（FETCH_STATUS=0）BEGININSERTdbo.Blocking_SqlTextSELECTSPID,CONVERT（nvarchar（4000）,text）,nowasCapture_Timestampfrom:

fn_get_sql（SQL_Handle）FETCHNEXTFROMcur_handleINTOSQL_Handle,SPIDENDCLOSEcur_HandleDEALLOCATEcur_HandleENDDROPtable#Blocks_rgENDGOUSEmsdb;

GOEXECdbo.sp_add_jobjob_name=NMonitorBlocking;

GOEXECsp_add_jobstepjob_name=NMonitorBlocking,step_name=Nexecuteblockingscript,subsystem=NTSQL,command=NexeccheckBlocking,database_name=NMonitorBlocking;

GOEXECsp_add_jobSchedulename=NScheduleBlockingCheck,job_name=NMonitorBlocking,freq_type=4,-dailyfreq_interval=1,freq_subday_type=4,freq_subday_interval=1EXECsp_add_jobserverjob_name=NMonitorBlocking,server_name=N（local）当Blocking发生一段时间后，我们可以查询下面的两个表格，以得知当时问题发生时的blocking信息:

useMonitorBlockingGOSELECT*fromBlocking_sqlTextSELECT*FROMBlocking_sysprocesses