SQLserver阻塞文档格式.docx
《SQLserver阻塞文档格式.docx》由会员分享，可在线阅读，更多相关《SQLserver阻塞文档格式.docx（44页珍藏版）》请在冰豆网上搜索。
如果使用的应用程序将
Transact-SQL语句透明地提交给服务器，则该应用程序必须提取所有结果行。
如果应用程序没这样做（如果无法配置它执行此操作），则可能无法解决阻塞问题。
为避免此问题，可以将这些应用程序限制在报表或决策支持数据库上。
5.分布式客户端/服务器死锁。
与常规死锁不同，分布式死锁无法由MicrosoftSQLServer?
2000自动检测到。
如果应用程序打开多个与SQLServer
的连接并异步提交查询，则可能会发生分布式客户端/服务器死锁。
例如，一个客户端应用程序线程有两个开放式连接。
该线程异步启动事务并在第一个连接上发出查询。
应用程序随后启动其它事务，在另一个连接上发出查询并等待结果。
当SQLServer返回其中一个连接的结果时，应用程序开始处理这些结果。
应用程序就这样处理结果，直到生成结果的查询被另一个连接上执行的查询阻塞而导致再没有可用的结果为止。
此时第一个连接阻塞，无限期等待处理更多的结果。
第二个连接没有在锁上阻塞，但仍试图将结果返回给应用程序。
然而，由于应用程序阻塞而在第一个连接上等待结果，第二个连接的结果将得不到处理。
避免阻塞方法
1.对每个查询使用查询超时。
2.对每个查询使用锁定超时。
有关更多信息，请参见自定义锁超时。
3.使用绑定连接。
有关更多信息，请参见使用绑定连接。
4.SQLServer本质上是受客户端应用程序操纵的傀儡。
客户端应用程序对服务器上获取的锁几乎有完全的控制（并对锁负责）。
虽然SQLServer
锁管理器自动使用锁保护事务，但这受客户端应用程序发出的查询类型和对结果的处理方式的直接鼓动。
因此，大多数阻塞问题的解决方案都涉及检查客户端应用程序。
5.阻塞问题常要求检查应用程序提交的SQL语句本身，以及检查与连接管理、所有结果行的处理等有关的应用程序行为本身。
如果开发工具不允许显式控制连接管理、查询超时、结果处理等，阻塞问题可能得不到解决。
设计应用程序以避免阻塞的准则包括
1.不要使用或设计使用户得以填写编辑框的应用程序，编辑框会生成长时间运行的查询。
例如，不要使用或设计提示用户输入的应用程序，允许某些字段保留空白或允许输入通配符。
这可能导致应用程序提交运行时间过长的查询，从而导致阻塞问题。
2.不要使用或设计使用户得以在事务输入容的应用程序。
3.允许取消查询。
4.使用查询或锁定超时，防止失控查询和避免分布式死锁。
5.立即完成提取所有结果行。
6.使事务尽可能简短。
7.显式控制连接管理。
8.在所预计的并发用户全负荷下对应用程序进行应力测试。
以下是一些相关的技术文档。
UnderstandingandResolvingSQLServer7.0or2000BlockingProblems
<
support.microsoft./default.aspx?
scid=kb;
en-us;
224453>
HOWTO:
TroubleshootApplicationPerformancewithSQLServer
224587>
了解和解决SQLServer7.0或2000阻塞问题--
本文的发布号曾为CHS224453
本页容
概要
更多信息
本文是对如下MicrosoftKnowledgeBase文章（针对SQLServer6.x）中的SQLServer7.0的更新：
162361INF：
了解和解决SQLServer阻塞问题
以上文章所包含的很多信息已经更新，并包括在SQLServer7.0BooksOnline的“UnderstandingandAvoidingBlocking”主题中。
继续阅读本文之前，请仔细检查这些信息，本文将不再重复这些容。
本文重点讨论如何监视SQLServer捕获相关的系统信息，以及如何分析信息以便成功地解决阻塞问题。
本文使用的标准术语与上面信息中定义的相同。
在本文的讨论中，术语“连接”是指数据库的单个已登录会话。
每个连接均以一个系统进程ID（SPID）出现。
每个SPID通常作为一个进程来引用，尽管在一般意义上它不是一个独立的进程环境。
更准确地说，每个SPID由响应指定客户端请求的单个连接时所需要的服务器资源和数据结构组成。
单个客户端应用程序可能有一个或多个连接。
从SQLServer的角度看，来自单个客户端计算机的单个客户端应用程序的多个连接，与来自多个客户端应用程序或多个客户端计算机的多个连接之间，没有什么区别。
一个连接可以阻塞另一个连接，这与它们是否出自同一个应用程序或出自两个不同的客户端计算机上的独立应用程序无关。
阻塞是具有基于锁定的并发特性的任何关系数据库管理系统（RDBMS）所不可避免的特征。
在SQLServer上，当一个SPID锁定了一个特定资源，而第二个SPID试图获得对同一资源的冲突锁定类型时，就会发生阻塞。
通常，第一个SPID锁定资源的时间围非常小。
当它释放锁定时，第二个连接即可顺利地获得对资源的自有锁定，并继续下一步操作。
这是正常情况，在一天中可能发生许多次，对系统性能没有任何明显的影响。
查询的持续时间和事务环境决定了锁定被占据的时间长短，因而也决定了对其它查询的影响。
如果查询不是在事务执行（并且没有使用任何锁定提示），对于SELECT语句引起的锁定，则只有当某个资源被实际读取时才会对该资源锁定，而不会在整个查询持续期间锁定该资源。
至于INSERT、UPDATE和DELETE语句，则在查询期间锁定它们，这既是为了数据的一致性，也是为了允许在必要时进行回滚查询。
对于事务执行的查询，决定锁定持续时间长短的因素包括：
查询类型、事务隔离级别以及查询中是否使用了锁定提示。
有关锁定、锁定提示和事务隔离级别的说明，请参见SQLServer7.0BooksOnline中的如下主题：
•
“UnderstandingLockinginSQLServer”
“LockingArchitecture”
“LockCompatibility”
“LockingHints”
“ChangingDefaultLockingBehaviorinOracleandSQLServer”
当锁定和阻塞增加到对系统性能产生不利影响的时候，通常是由于如下原因所至：
一个SPID锁定了一组资源，并且在释放锁之前持续了很长一段时间。
这种类型的阻塞在一段时间后自行消失，但会导致性能降低。
一个SPID锁定了一组资源，并且不再释放它们。
这种类型的阻塞不会自行消失，并且会无限期地阻止对受影响资源的访问。
在上面第一种情况中，一段时间后SPID释放锁定时阻塞问题将自行消失。
但是，情况可能是变化多端的，因为一段时间后不同的SPID会阻塞不同的资源，总是产生变化的目标。
由于这个原因，这些情况很难使用SQLServerEnterpriseManager或单独的SQL查询来解决问题。
而第二种情况则产生更便于诊断的持续状态。
收集阻塞信息
要减少解决阻塞问题的难度，数据库管理员可以使用SQL脚本连续地监视SQLServer上的锁定和阻塞状态。
这些脚本可以提供一段时间后指定实例的快照，从而帮助用户全面了解存在的问题。
关于如何用SQL脚本监视阻塞的说明，请参见下面的MicrosoftKnowledgeBase文章：
251004INF：
如何监视SQLServer7.0阻塞
271509INF：
如何监视SQLServer2000阻塞
本文中的脚本将执行下面的任务。
在可能的情况下，我们还提供通过EnterpriseManager或特定SQL查询来获得该信息的方法。
1.
找出阻塞链源头的SPID。
除了使用上面提到的脚本外，还可以使用SQLEnterpriseManager找出阻塞链源头的SPID，方法如下：
a.
展开服务器组，然后展开服务器。
b.
展开Management，然后展开CurrentActivity。
c.
展开Locks/ProcessID。
SPID及其阻塞信息将显示在详细信息窗格中。
正在造成阻塞的SPID将显示为“（Blocking）”。
但请注意，有时需要使用脚本查询而不使用EnterpriseManager，因为某些类型的tempdb阻塞问题可能会阻止您运行使用临时表操作的查询。
使用直接查询可以给您提供必要的控制能力，以便避免出现该问题。
2.
查找引起阻塞的SPID正在运行的查询。
脚本方法使用如下查询来确定特定SPID发出的命令：
DBCCINPUTBUFFER（<
spid>
）
另外一种方法，可以按如下步骤使用SQLEnterpriseManager：
单击ProcessInfo。
SPID将显示在详细信息窗格中。
d.
双击引起阻塞的SPID即可看见该SPID执行的最后一个Transact-SQL命令组。
3.
查找引起阻塞的SPID当前占用的锁定类型。
执行sp_lock系统存储过程，即可找到该信息。
另外一种方法，可以按如下步骤使用EnterpriseManager获得该信息：
SPID以及正在使用的锁定的相关信息将显示在详细信息窗格中。
4.
查找引起阻塞的SPID的事务嵌套级别和进程状态。
SPID的事务嵌套级别可以在TRANCOUNT全局变量中找到。
但是，通过按如下方式查询sysprocesses表，可以从SPID以外找到它。
SELECTopen_tranFROMSYSPROCESSESWHERESPID=<
blockingSPIDnumber>
go
返回的值为该SPID的TRANCOUNT值。
该值显示阻塞SPID的事务嵌套级别，反过来可以解释为什么SPID正在使用锁定。
例如，如果该值大于零，则SPID位于事务中间（这种情况下，根据事务隔离级别，可以预计它将保持已经获得的某些锁定）。
要查看数据库中是否存在任何长时间打开的事务，还可以使用DBCCOPENTRANdatabase_name。
收集SQLServer事件探查器跟踪信息
除以上信息外，通常还需要捕获服务器上各种活动的“事件探查器”跟踪文件，以便彻底调查SQLServer上的阻塞问题。
如果SPID在事务执行了多个语句，那么只有最后一个语句出现在DBCCINPUTBUFFER输出中。
但是，有可能是更前面的某个命令导致锁定仍然被占据。
“事件探查器”跟踪文件将使您能看见由SPID在当前事务执行的所有命令。
下面的步骤将帮助您设置SQLServer事件探查器以便捕获踪迹。
打开SQLServer事件探查器。
在Tools菜单上，单击Options。
确保选中AllEventClasses和AllDataColumns选项。
单击OK。
5.
在File菜单中，指向New，然后单击Trace。
6.
在General选项卡上，指定要捕获数据的跟踪名称和文件。
7.
在Events选项卡上，将如下事件类型添加到跟踪中：
ErrorandWarning
Exception
该事件表示发生了异常错误。
严重度低于25的异常错误表示错误已从SQLServer返回客户端。
严重度为25的异常错误是部的SQLServer异常错误，如下所述应当被筛选掉。
Misc.
Attention
该事件表示已经发出一个注意信号。
发出注意信号的一般原因是发生客户端取消操作或查询超时。
Sessions
Connect
该事件表示已建立一个新连接。
Disconnect
该事件表示一个客户端已经断开连接。
ExistingConnection
该事件表示当“SQL事件探查器”跟踪启动时存在一个连接。
TSQL
RPC:
Starting
该事件表示已经开始执行远程过程调用（RPC）。
SQL:
BatchStarting
该事件表示已经开始执行Transact-SQL命令组。
StoredProcedures
SP:
StmtStarting
该事件表示存储过程中的语句正在开始执行。
存储过程名位于事件的Text部分的开头。
另外，您可以包括如下事件以便获得更进一步的信息。
如果当前的运行环境是高数据量运营环境，那么可以决定只使用以上事件，因为有了它们足以能解决阻塞问题了。
如果还包括以下的附加事件，可以使您能更方便迅速地找出问题的根源，但这也会增加系统负载并增加跟踪输出的大小。
ExecutionPlan
该事件显示被执行的Transact-SQL语句的计划树。
Transactions
DTCTransaction
该事件跟踪两个或多个数据库或服务器之间的Microsoft分布式事务处理协调器（MSDTC）事务。
SQLTransaction
该事件跟踪SQLBEGIN、SAVE、COMMIT和ROLLBACKTRANSACTION语句。
Completed
该事件表示远程过程调用（RPC）已经执行完毕。
BatchCompleted
该事件表示Transact-SQL命令组已经执行完毕。
StmtCompleted
该事件表示存储过程中的语句已经执行完毕。
8.
在DataColumns选项卡上，确保包括了如下列：
StartTime、EndTime、ConnectionID、SPID,EventClass、Text、IntegerData、BinaryData、ApplicationName、NTUserName和SQLUserName。
如果包括上面第二个表中的附加事件，则还要包括以下数据列：
Duration、CPU、Reads和Writes。
9.
在Filters选项卡上，排除SQLServer部异常错误。
在TraceEventCriteria框中，选择Severity并在Maximum框中键入24。
然后单击OK。
有关监视从SQLServer发送到客户端的错误消息的详细信息，请参见下面的MicrosoftKnowledgeBase文章：
199037INF：
跟踪从SQLServer发送到客户端的错误消息
有关使用“事件探查器”的信息，请参见SQLServerBooksOnline。
识别和解决常见的阻塞情形
通过查看以上信息，可以找出大多数阻塞问题的原因。
本文下面的容将讨论如何使用这些信息来识别并解决常见的阻塞情形。
该讨论假设您已经使用文章Q251004（前面已提到）中的阻塞脚本来捕获有关阻塞SPID的信息，并已经使用上面提到的事件制作了“事件探查器”跟踪。
查看阻塞脚本输出
检查sysprocesses输出，以找到阻塞链的开头。
如果没有为阻塞脚本指定快速模式，则在脚本输出中会有一个标题为“SPIDsattheheadofblockingchains”的段落列出引起阻塞的SPID：
在阻塞链的开头的SPID
spid
------
9
10
如果指定了快速选项，仍然可以通过查看sysprocesses输出来找出阻塞头。
以下容摘自sysprocesses输出：
spidstatusblocked
9sleeping0
10sleeping0
11sleeping13
12sleeping10
13sleeping9
14sleeping12
其中，可以在blocked列中看见SPID9和SPID10均为0，这表示它们当前没有被阻塞，但它们出现在其它SPID的blocked列中。
这表示SPID9和SPID10分别位于独立阻塞链的开头。
检查sysprocesses输出中有关位于阻塞链开头的SPID的信息。
一定要检查如下sysprocesses字段：
Status
该列快速展示特定SPID的状态。
通常，状态sleeping表示SPID已经执行完毕，并且正在等候应用程序提交另一个查询或命令组。
状态runnable表示SPID当前正在处理查询。
下表简单解释各种状态值。
Background
SPID正在执行后台任务。
Sleeping
SPID当前没有执行任务。
通常，这表示SPID正在等待应用程序的命令。
Runnable
SPID当前正在执行任务。
Dormant
与Sleeping相同，但Dormant还表示在完成RPC事件后SPID已被重置。
重置将清除RPC事件期间所使用的资源。
这是个正常状态，并且SPID可用，正在等待执行后续命令。
Rollback
SPID在事务的回滚过程中。
Defwakeup
表示SPID正在等待处于释放过程中的资源。
waitresource字段应当表示提到的资源。
Spinloop
在试图获得用于SMP系统并发控制的旋转锁定（spinlock）时进程正处于等待中。
Open_tran
该字段告诉您SPID的事务嵌套级别。
如果该值大于0，则SPID位于一个打开的事务中，并且可能正在占用由事务中的任何语句获得的锁定。
Lastwaittype、waittype和waittime
lastwaittype字段告诉您SPID的最后一个waittype或当前waittype。
该字段是SQLServer7.0中的新字段，是waittype字段的字符串表现形式（waittype是被保留的部二进制列）。
如果waittype是0x0000，那么SPID当前没有等待任何任务，并且lastwaittype的值表示SPID拥有的最后一个waittype。
如果waittype为非零，则lastwaittype的值表示SPID的当前waittype。
有关不同lastwaittype和waittype值的简要说明，请参见下面的MicrosoftKnowledgeBase文章：
244455INF：
SysprocessesWaittype和Lastwaittype字段的定义
waittime值可以用来确定SPID是否正在执行任务。
如果在查询sysprocesses表时返回waittime列中的值，而该值小于从上一个sysprocesses查询所获得的waittime值，那么，这表示前面的锁定已被获得并被释放，现在正在等候新的锁定（假设是非零的waittime）。
通过比较各个sysprocesses输出中的waitresource，可以对此进行确认。
Waitresource
该字段表示SPID正在等候的资源。
下表列出了waitresource常见格式及其意义：
Table
DatabaseID:
ObjectID
TAB:
5:
261575970
这里，数据库ID5是pubs示例数据库，对象ID261575970是titles表。
Page
FileID:
PageID
PAG:
1:
104
这里，数据库ID5是pubs，文件ID1是主数据文件，而页104是属于titl
升级会员 