最大程度降低 SQL Server 中的阻塞

 

锁定对支持数据库的并发读写活动而言是必需的，但阻塞却会对系统性能产生负面影响，有时让人难以觉察。在本文中，我将介绍如何优化 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，它可以禁用单个语句的锁升级。