最大程度降低 SQL Server 中的阻塞
时间：2010-10-06 来源：老玉米
Cherié Warren
概览:
- 发生锁升级的原因
- 避免不必要的阻塞
- 优化查询
- 监视锁定对性能的影响
锁定对支持数据库的并发读写活动而言是必需的，但阻塞却会对系统性能产生负面影响，有时让人难以觉察。在本文中，我将介绍如何优化 SQL Server 2005 或 SQL Server 2008 数据库以最大程度降低
阻塞，以及如何监视系统以更好地了解锁定如何对性能产生影响。
锁定和升级
SQL Server® 根据受影响的记录数和系统中存在的并发活动来选择最合适的锁粒度。默认情况下，SQL Server 会选择尽可能小的锁粒度，如果它可以更高效地使用系统内存，则仅选择粗粒度锁。只有当锁升级会对总体系统性能带来好处时，SQL Server 才会升级锁。如图 1 所示，当特定扫描中锁的数量超过 5,000 或者系统用于锁的内存数超过可用容量时，将会开始升级：
Figure 1 促使锁升级的条件 (单击该图像获得较大视图)
- 当锁设置为 0 时，数据库引擎使用 24 % 的非地址窗口化扩展（非 AWE）内存
- 当锁设置非 0 时，数据库引擎使用 40 % 的非 AWE 内存
如果确实发生了升级，则始终会升级到表级锁。
避免不必要的阻塞
阻塞在任何锁粒度情况下都可能发生，但阻塞的显现程度却在升级后增加。锁升级可能是一个信号，说明您的应用程序在设计、编码或配置方面的效率不高。
遵循数据库设计基本原理（例如，使用采用窄键的标准化架构以及避免在事务性系统中执行大量数据操作）是避免发生阻塞的重要手段。如果未遵循这些原理（例如，将报告系统从事务性系统中分离出来，或在非工作时间处理数据馈送），则系统优化将会很困难。
索引编制可能是决定访问数据所需锁数量的关键因素。索引可以通过减少数据库引擎必须执行的内部查找次数来减少查询访问的记录数。例如，如果您从一个表的非索引列选择一行，则表中的每一行都需要被临时锁定，直到确定了所需的记录为止。但是，如果对该列编制了索引，则仅需要单个锁。
SQL Server 2005 和 SQL Server 2008 都包含动态管理视图（sys.dm_db_missing_index_group_stats、 sys.dm_db_missing_index_groups、sys.dm_db_missing_index_details），这些视图根据累计使用情况统计来显示那些将会得益于索引的表和列。
碎片也可能是一个隐含的性能问题，如果碎片过多，数据库引擎可能需要访问比采用其他方式更多的页面。此外，不正确的统计信息也可能会导致查询优化器选择效率不高的计划。
请记住，尽管索引可以加快数据访问速度，但它们也会降低数据修改速度，因为不但基础数据需要修改，而且索引也需要更新。动态管理视图 sys.dm_db_index_usage_stats 重点强调索引的使用频率。一个常见的低效索引编制示例是复合索引，其中对同一列分别以单独和组合方式编制了索引。由于 SQL Server 从左到右访问索引，因此只要最左侧的列有用即可使用该索引。
分区表既可以优化系统（以便减少暴露的块），也可以将数据分成可以分别争取的单独物理对象。尽管启用行分区是一种较为直观的数据分离方法，但水平分区数据也是一种可供考虑的选项。您可能想通过将一个表分成多个行数和键值相同而列数不同的分离表的方式来选择进行反规范化，以降低不同进程在相同时刻试图独占访问数据的机会。
应用程序访问特定数据行的方法越多、该行中可能包含的列越多，列分区方法就越有吸引力。应用程序队列和状态表有时可以从这种方法中受益。SQL Server 2008 新增了按分区（如果没有为表启用分区则按表）禁用锁升级的功能。
查询优化
查询优化在提高性能方面起着举足轻重的作用。下面是可以采用的三种方法：
缩短事务 要想降低阻塞并改进整体性能，最重要的方法之一是确保事务尽可能小。任何不是很看重事务完整性的处理过程（如查找相关数据、编制索引以及擦除数据）都可以考虑减小其大小。
SQL 将每个语句都作为隐式事务。如果该语句影响大量的行，则单一语句仍可构成一个大型事务，尤其是当涉及许多列时或列中包含大数据类型时更是如此。如果填充因子较高或 UPDATE 语句要填充的列的宽度大于所分配的值，则单一语句可能还会导致页面分割。在这种情况下，将事务分割成行组非常有用，分割完后，每次处理一个直到全部处理完为止。仅当单个语句或语句组可以分成较小的批次（无论成功或失败，都可以视为完成了一个工作单元）时，才应考虑采用批处理方式。
对事务进行排序 在事务中，人为对语句进行排序可以降低发生阻塞的可能性。有两条规则应牢记。首先，应该以相同的顺序访问系统中所有 SQL 代码内的对象。如果顺序不一致，当两个竞争进程以不同顺序访问数据时就可能会出现死锁，导致其中一个进程出现系统错误。其次，将访问频繁或访问代价高昂的对象放置在事务末端。当事务中需要这些对象时 SQL 才会锁定它们。通过延迟对“热点”的访问，可以减少这些对象持有锁的时间。
使用锁定提示 锁定提示可在会话级别或语句级别用于特定的表或视图。使用会话级别提示的一种典型情况是数据仓库中的批处理操作，在此操作中，开发人员知道该进程将是在给定时间运行该数据集的唯一进程。通过在存储过程的开头使用诸如 SET ISOLATION LEVEL READ UNCOMMITTED 等命令，SQL Server 将不会保留任何读取锁，从而降低整体锁定开销并提高性能。
使用语句级别提示的一种典型情况是当开发人员知道脏读可以安全进行时（例如当从表中读取某一行而其他并发流程肯定不会需要该行时）或者当所有其他性能调整努力均失败（架构设计、索引设计和维护以及查询调整）而开发人员希望强制编译器使用特定类型的提示时。
如果监视结果表明已出现较大粒度锁，而其中很少有记录受到查询的影响，则行级锁提示可能会很有效，因为这可以减少阻塞的发生。如果监视结果表明当表中几乎所有记录都受查询影响时，较小粒度锁正处于被持有状态（而非升级），则表级锁提示可能会很有效，因为这可能会减少持有锁所需的系统资源。请注意，指定锁定提示并不能保证当锁数量达到系统内存阈值时不会升级。但是，这样做会阻止所有其他升级。
调整您的配置
如图 2 所示，配置 SQL Server 系统时需要考虑很多因素。
Figure 2 SQL Server 如何确定可用于锁定的内存量 (单击该图像获得较大视图)
内存 锁始终保留在非 AWE 内存中，因此增加非 AWE 内存的大小会增强系统持有锁的能力。
由于 32 位体系结构受限于 4GB 非 AWE 内存而 64 位却无任何限制，因此应将 64 位体系结构作为尝试增强锁定能力时的首选。
在 32 位系统中，可以通过在 Boot.ini 文件中添加 /3GB 开关来从 SQL Server 的操作系统中获取 1 GB 的额外内存。
SQL Server 配置设置 影响锁定的各种设置均可通过 sp_configure 进行调整。锁设置可以配置在引发错误之前系统可以持有的锁数量。默认情况下设置为 0，这意味着服务器将动态调整其他竞争内存的进程所保留的锁。SQL 开始时将保留 2,500 个锁，每个锁占用 96 字节的内存。分页内存不会被使用。
最小和最大内存设置将保留 SQL Server 所使用的内存量，因此将服务器配置为静态占有内存。由于锁升级与可用内存有关，因此在判断是否会发生升级时，竞争进程所保留的内存量会对此产生影响。
连接设置 默认情况下，被阻塞的锁不会超时，但您可以使用 @@LOCK_TIMEOUT 设置，如果超过指定的释放锁等待阈值，则该设置会引发错误。
跟踪标记 两个跟踪标记都与锁升级密切相关。一个是跟踪标记 1211，它可以禁用锁升级。如果消耗的锁数超过可用内存，则会引发错误。另一个是跟踪标记 1224，它可以禁用单个语句的锁升级。
查看您的系统
更多参考资料
- Inside Microsoft SQL Server 2005:The Storage Engine，作者：Kalen Delaney
- 如何监视 SQL Server 2005 和 SQL Server 2000 中的阻塞
- SQL Server 2005 性能统计脚本
- 如何解决由 SQL Server 中锁升级导致的阻塞问题
- 锁升级（数据库引擎）
对于块和锁，要监视其中的锁定对整体系统性能的影响，可在每次经过给定时间间隔（可能是一小时）后，轮询状态数据并捕捉被持有锁上的运行统计信息。要捕捉的关键信息包括：
- 受影响对象、粒度和锁类型
- 锁和块的持续时间
- 发出的 SQL 命令（存储过程名称、SQL 语句 within）
- 有关阻塞链的信息（如果相关的话）
- 系统消耗其可用锁定能力的方式
您可以运行类似于图 3 所示的脚本来捕捉此信息，并将其写入具有相应时间戳的表中。要进一步降低被阻塞数据的 ResourceId，您可以运行类似于图 4 所示的脚本。
Figure 4 Learning more about blocked data
DECLARE @SQL nvarchar(max)
, @CallingResourceType varchar(30)
, @Objectname sysname
, @DBName sysname
, @resource_associated_entity_id int
-- TODO: Set the variables for the object you wish to look up
SET @SQL = N'
USE ' + @DbName + N'
DECLARE @ObjectId int
SELECT @ObjectId = CASE
WHEN @CallingResourceType = ''OBJECT''
THEN @resource_associated_entity_id
WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
THEN (SELECT object_id
FROM sys.partitions
WHERE hobt_id = @resource_associated_entity_id)
WHEN @CallingResourceType = ''ALLOCATION_UNIT''
THEN (SELECT CASE
WHEN type IN (1, 3)
THEN (SELECT object_id
FROM sys.partitions
WHERE hobt_id = allocation_unit_id)
WHEN type = 2
THEN (SELECT object_id
FROM sys.partitions
WHERE partition_id = allocation_unit_id)
ELSE NULL
END
FROM sys.allocation_units
WHERE allocation_unit_id = @resource_associated_entity_id)
ELSE NULL
END
SELECT @ObjectName = OBJECT_NAME(@ObjectId)'
EXEC dbo.sp_executeSQL
@SQL
, N'@CallingResourceType varchar(30)
, @resource_associated_entity_id int
, @ObjectName sysname OUTPUT'
, @resource_associated_entity_id = @resource_associated_entity_id
, @CallingResourceType = @CallingResourceType
, @ObjectName = @ObjectName OUTPUT
Figure 3 Capturing locking stats
SELECT er.wait_time AS WaitMSQty
, er.session_id AS CallingSpId
, LEFT(nt_user_name, 30) AS CallingUserName
, LEFT(ces.program_name, 40) AS CallingProgramName
, er.blocking_session_id AS BlockingSpId
, DB_NAME(er.database_id) AS DbName
, CAST(csql.text AS varchar(255)) AS CallingSQL
, clck.CallingResourceId
, clck.CallingResourceType
, clck.CallingRequestMode
, CAST(bsql.text AS varchar(255)) AS BlockingSQL
, blck.BlockingResourceType
, blck.BlockingRequestMode
FROM master.sys.dm_exec_requests er WITH (NOLOCK)
JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
ON er.session_id = ces.session_id
CROSS APPLY fn_get_sql (er.sql_handle) csql
JOIN (
-- Retrieve lock information for calling process, return only one record to
-- report information at the session level
SELECT cl.request_session_id AS CallingSpId
, MIN(cl.resource_associated_entity_id) AS CallingResourceId
, MIN(LEFT(cl.resource_type, 30)) AS CallingResourceType
, MIN(LEFT(cl.request_mode, 30)) AS CallingRequestMode
-- (i.e. schema, update, etc.)
FROM master.sys.dm_tran_locks cl WITH (nolock)
WHERE cl.request_status = 'WAIT' -- Status of the lock request = waiting
GROUP BY cl.request_session_id
) AS clck
ON er.session_id = clck.CallingSpid
JOIN (
-- Retrieve lock information for blocking process
-- Only one record will be returned (one possibility, for instance,
-- is for multiple row locks to occur)
SELECT bl.request_session_id AS BlockingSpId
, bl.resource_associated_entity_id AS BlockingResourceId
, MIN(LEFT(bl.resource_type, 30)) AS BlockingResourceType
, MIN(LEFT(bl.request_mode, 30)) AS BlockingRequestMode
FROM master.sys.dm_tran_locks bl WITH (nolock)
GROUP BY bl.request_session_id
, bl.resource_associated_entity_id
) AS blck
ON er.blocking_session_id = blck.BlockingSpId
AND clck.CallingResourceId = blck.BlockingResourceId
JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
ON er.blocking_session_id = ber.session_id
CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE ces.is_user_process = 1
AND er.wait_time > 0
您还可以通过 SQL Profile（Lock:Escalation 事件）、dm_db_index_operational_stats 动态管理视图 (index_lock_promotion_count) 或系统锁定信息的定期轮询来监视系统升级。需要从升级监视中收集的信息主要是该流程能否保证升级；如果不能，相应的存储过程将查明产生性能问题的根源。具有大量数据或高并发使用量的表应作为主要升级对象。
在收集了有关锁定、阻塞和升级的数据后，可以对该数据进行分析以确定每个对象的累积块和锁时间（发生次数乘以发生时的持续时间）。通常，这可以启动性能调整的迭代循环，在此过程中将部署、监视、分析和修复变更。有时，它只需进行一个简单的改动（例如添加一个索引）即可显著改善性能并改变系统中会导致最令人头疼的性能瓶颈问题的区域。
有关降低 SQL Server 阻塞的更多信息，请参阅“更多参考资料”侧栏。如果在整个设计、编码和稳定阶段中始终注意尽可能使事务保持最小，则可以将许多阻塞问题降至最低。使用合适的硬件也可以显著降低出现不必要升级的可能性。不管怎样，系统中的当前阻塞评估都可以迅速确定其实际的性能问题。
Cherié Warren 是 Microsoft IT 的高级开发主管。她目前正负责领导 Microsoft 一个大型事务数据库的开发。Cherié 还经常探究阻塞产生的根本原因并着手解决与阻塞相关的性能问题。她作为一名企业级 SQL Server 数据库专家已有 10 年的时间。
© 2008 Microsoft Corporation 与 CMP Media, LLC.保留所有权利；不得对全文或部分内容进行复制.
相关阅读 更多 +
