诊断和解决 SQL Server 上的闩锁争用问题

本指南介绍如何识别和解决在具有特定工作负荷的高并发系统上运行 SQL Server 应用程序时观察到的闩锁争用问题。

随着服务器上 CPU 内核数量的持续增加，并发性也相应增加，这可能会在数据库引擎内必须以串行方式访问的数据结构上引入争用点。 对于高吞吐量/高并发事务处理 (OLTP) 工作负荷，尤其如此。 有几种工具、技术和方法可以应对这些挑战，以及在设计应用程序时可以遵循的做法，这些做法可能有助于完全避免这些挑战。 本文介绍一种特定类型的数据结构争用，这种争用使用自旋锁来序列化对这些数据结构的访问。

什么是 SQL Server 闩锁争用？

闩锁是 SQL Server 引擎使用的轻量级同步基元，可确保内存中结构（包括索引、数据页）和内部结构（例如 B 树中的非叶页）的一致性。 SQL Server 使用缓冲区闩锁来保护缓冲池中的页，使用 I/O 闩锁来保护尚未载入缓冲池的页。 每当在 SQL Server 缓冲池的页中写入或读取数据时，工作线程必须首先获取该页的缓冲区闩锁。 有各种缓冲闩锁类型可用于访问缓冲池中的页面，包括排他闩锁（PAGELATCH_EX）和共享闩锁（PAGELATCH_SH）。 当 SQL Server 尝试访问缓冲池中尚不存在的页面时，会发布异步 I/O 以将页面加载到缓冲池中。 如果 SQL Server 需要等待 I/O 子系统做出响应，它将根据请求类型等待排他 （） 或共享 （PAGEIOLATCH_EXPAGEIOLATCH_SH） I/O 闩锁;这样做是为了防止另一个工作线程将同一页加载到具有不兼容闩锁的缓冲池中。 闩锁还用于保护对缓冲池页以外的内部内存结构的访问；这些闩锁称为非缓冲区闩锁。

页闩锁上的争用是多 CPU 系统上遇到的最常见情况；因此本文的大部分内容都集中在这些方面。

当多个线程同时试图获取同一内存中结构的不兼容闩锁时，就会发生闩锁争用。 由于闩锁是一种内部控制机制；因此 SQL 引擎会自动确定何时使用它们。 由于闩锁的行为是确定性行为，因此应用程序决策（包括架构设计）可以影响该行为。 本文旨在提供了以下信息：

• 有关 SQL Server 如何使用闩锁的背景信息。
• 用于调查闩锁争用的工具。
• 如何确定观察到的争用量是否有问题。

我们将讨论一些常见方案，以及如何最好地处理它们以缓解争用。

SQL Server 如何使用闩锁？

SQL Server 中的页面大小为 8 KB，可以存储多行。 为了提高并发性和性能，缓冲区闩锁只在页上的物理操作期间被持有，而不像锁那样在逻辑事务期间被持有。

闩锁是 SQL 引擎的内部机制，用于提供内存一致性；而锁则是 SQL Server 用来提供逻辑事务一致性。 下表将闩锁与锁进行了比较：

SQL Server 闩锁模式和兼容性

某些闩锁争用是 SQL Server 引擎正常运行的一部分。 在高并发系统上，不可避免地会出现多个不同兼容性的并发闩锁请求。 SQL Server 通过要求不兼容的闩锁请求在队列中等待，直到未完成的闩锁要求完成，来强制闩锁兼容性。

闩锁可以通过五种不同模式之一获得，这些模式与访问级别有关。 SQL Server 闩锁模式可以概括如下：

• KP：保持闩锁。 确保引用的结构不会被销毁。 当线程想要查看缓冲区结构时使用。 由于 KP 闩锁与除销毁（DT）闩锁之外的所有闩锁兼容，因此 KP 闩锁被视为 轻量级，这意味着在使用时对性能的影响很小。 由于 KP 闩锁与 DT 闩锁不兼容，因此它可防止任何其他线程销毁引用的结构。 例如，KP 闩锁可防止它引用的结构被惰性编写器进程销毁。 有关如何将惰性编写器进程与 SQL Server 缓冲区页面管理一起使用的更多信息，请参阅在数据库引擎中编写页面。

• SH：共享闩锁。 需要读取引用的结构（例如，读取数据页）。 多个线程可以同时访问共享闩锁下的资源进行读取。

• UP：更新闩锁。 与 SH（共享闩锁）和 KP 兼容，但与其他闩锁不兼容；因此不允许 EX 闩锁写入引用的结构。

• EX：独占锁。 阻止其他线程对引用的结构进行写入或读取。 一个使用示例是修改页面内容以保护残缺页。

• DT：销毁闩锁。 必须在销毁引用结构的内容之前获取。 例如，在将 DT 闩锁添加到可供其他线程使用的空闲缓冲区列表之前，惰性编写器进程必须获取 DT 闩锁，以释放干净的页面。

闩锁模式具有不同级别的兼容性，例如，共享闩锁（SH）与更新（UP）或保持（KP）闩锁兼容，但与销毁闩锁（DT）不兼容。 只要闩锁兼容，就可以在同一结构上同时获取多个闩锁。 当线程试图获取以不兼容模式持有的闩锁时，它会被放入队列中等待指示资源可用的信号。 类型为 SOS_Task 的自旋锁用于通过强制对队列进行序列化访问来保护等待队列。 必须获取此自旋锁才能将项目添加到队列中。 当不兼容的闩锁被释放时，SOS_Task 自旋锁还会向队列中的线程发出信号，允许等待的线程获取兼容的闩锁并继续工作。 随着闩锁请求的释放，等待队列在先进先出 (FIFO) 的基础上进行处理。 闩锁遵循此 FIFO 原则，以确保公平性并防止线程饥饿。

下表中列出了闩锁模式兼容性（是 表示兼容性， 否 表示不兼容）：

SQL Server 超级锁存器 和 子锁存器

随着基于 NUMA 的多个套接字/多核系统的日益存在，SQL Server 2005 引入了超级平台（也称为子平台），这些子平台仅在具有 32 个或多个逻辑处理器的系统上有效。 超闩锁可提高 SQL 引擎在高度并发 OLTP 工作负荷中的某些使用模式的效率；例如，当某些页面具有重度只读共享 (SH) 访问模式时，但很少写入。 具有此类访问模式的页面示例是 B 树（即索引）根页，当 B 树中的任何级别发生页面拆分时，SQL 引擎要求在根页上持有共享闩锁。 在插入密集型和高并发 OLTP 工作负荷中，页面拆分数随吞吐量而广泛增加，这可降低性能。 超闩锁可以提高访问共享页面的性能，其中多个并发运行的工作线程需要 SH 个闩锁。 为此，SQL Server 引擎会将此类页面上的闩锁动态升级为超级闩锁。 Superlatch 将单个闩锁分区成一个子闩锁结构数组，每个 CPU 核心每个分区一个子闩锁，其中主闩锁充当代理转发器，而只读闩锁不需要全局状态同步。 为此，始终分配给特定 CPU 的工作器只需获取分配给本地计划程序的共享 (SH) 子闩锁。

与非分区共享闩锁相比，获取兼容闩锁（如共享超闩锁）使用更少的资源，并更好地缩放对热页的访问，因为删除全局状态同步要求可以通过仅访问本地 NUMA 内存来显著提高性能。 相反，获取排他型 (EX) 超闩锁比获取 EX 常规闩锁要昂贵得多；因为 SQL 必须在所有子闩锁之间发出信号。 当观察到超闩锁使用大量 EX 访问模式时，SQL 引擎可以在从缓冲池中丢弃页面后将其降级。 下图描绘了普通闩锁和分区式Superlatch：

使用性能监视器中的 SQL Server：Latches 对象和关联的计数器收集有关超级闩锁的信息，包括超级闩锁的数量、每秒超级闩锁推广数和每秒超级闩锁降级数。 有关 SQL Server：Latches 对象和相关计数器的详细信息，请参阅 SQL Server、Latches 对象。

闩锁等待类型

累积等待信息由 SQL Server 跟踪，可使用动态管理视图 (DMW) sys.dm_os_wait_stats 进行访问。 SQL Server 采用三种闩锁等待类型，如 wait_type DMV 中相应的 sys.dm_os_wait_stats 所定义：

• 缓冲区 (BUF) 闩锁：用于保证用户对象的索引页和数据页的一致性。 它们还用于保护对 SQL Server 用于系统对象的数据页的访问。 例如，管理分配的页受缓冲区闩锁保护。 其中包括页可用空间 (PFS) 页、全局分配映射 (GAM) 页、共享全局分配映射 (SGAM) 页和索引分配映射 (IAM) 页。 sys.dm_os_wait_stats 中缓冲区闩锁报告为 wait_type 的 PAGELATCH_*。

• 非缓冲区 (Non-BUF) 闩锁：用于保证除缓冲池页以外的任何内存中结构的一致性。 任何非缓冲区闩锁的等待情况都被报告为 wait_type 的 LATCH_*。

• IO 闩锁：缓冲区闩锁的一个子集，当这些结构需要通过 I/O 操作加载到缓冲池中时，它们保证受缓冲闩锁保护的相同结构的一致性。 IO 闩锁可防止另一个线程使用不兼容的闩锁将同一页加载到缓冲池中。 与 wait_type 的 PAGEIOLATCH_* 关联。

  注意

  如果看到大量 PAGEIOLATCH 等待，则表示 SQL Server 正在等待 I/O 子系统。 虽然预期会有一定数量的 PAGEIOLATCH 等待和正常行为，但如果平均 PAGEIOLATCH 等待时间持续超过 10 毫秒（ms），则应调查 I/O 子系统承受压力的原因。

如果在检查 sys.dm_os_wait_stats DMV 时遇到非缓冲区闩锁，则还必须检查 sys.dm_os_latch_stats，以获取非缓冲区闩锁的累积等待信息的详细明细。 所有缓冲区闩锁等待都分类在 BUFFER 闩锁类下，其余的用于对非缓冲区闩锁进行分类。

SQL Server 闩锁争用的表现和原因

在繁忙的高并发系统中，在 SQL Server 中频繁访问并受闩锁和其他控制机制保护的结构上看到主动争用是很正常的。 当与获取页闩锁相关的争用和等待时间足以降低资源 (CPU) 利用率，从而阻碍吞吐量时，这被认为是有问题的。

闩锁争用示例

在下图中，蓝线表示 SQL Server 中的吞吐量，以每秒事务数为单位；黑线表示页闩锁平均等待时间。 在这种情况下，每个事务都会对具有顺序递增前导值的聚集索引执行一个 INSERT，例如在填充数据类型 bigint 的 IDENTITY 列时。 由于 CPU 数增加到 32，很明显，总体吞吐量已减少，页闩锁等待时间已增加到大约 48 毫秒，如黑线所示。 吞吐量和页闩锁等待时间之间的这种反比关系是一种很容易诊断的常见情况。

解决闩锁争用后的性能

如下图所示，SQL Server 不再受页闩锁等待的瓶颈限制，并且以每秒事务数衡量的吞吐量增加了 300%。 这是通过本文后面介绍的对计算列使用哈希分区方法实现的。 这种性能改进针对的是具有大量内核和并发率高的系统。

影响闩锁争用的因素

导致 OLTP 环境性能不佳的闩锁争用通常是由与以下一个或多个因素相关的高并发引起的：

诊断 SQL Server 闩锁争用

本部分提供有关诊断 SQL Server 闩锁争用的信息，以确定其是否对您的环境造成影响。

诊断闩锁争用的工具和方法

用于诊断闩锁争用的主要工具包括：

• 性能监视器用于监视 SQL Server 中的 CPU 利用率和等待时间，并确定 CPU 使用率与闩锁等待时间之间是否存在关系。

• SQL Server DMV，可用于确定导致问题的特定闩锁类型和受影响的资源。

• 在某些情况下，必须使用 Windows 调试工具来获取和分析 SQL Server 进程的内存转储。

诊断闩锁争用的技术流程可以概括为以下步骤：

1. 确定是否存在可能与闩锁相关的争用。

2. 使用附录：SQL Server 闩锁争用脚本中提供的 DMV 视图来确定闩锁类型和受影响的资源。

3. 使用处理不同表模式的闩锁争用中所述的方法之一来缓解争用。

闩锁争用指标

如前所述，仅当获取页闩锁时出现的争用和等待时间在 CPU 资源可用的情况下导致吞吐量无法提高时，才认为闩锁争用有问题。 确定可接受的争用量需要采用一种综合方法，该方法应考虑性能和吞吐量要求以及可用的 I/O 和 CPU 资源。 本部分将指导你确定闩锁争用对工作负荷的影响，如下所示：

1. 测量代表性测试期间的总等待时间。
2. 对其进行排序。
3. 确定与闩锁相关的等待时间比例。

累积等待信息可从 sys.dm_os_wait_stats DMV 获得。 闩锁争用最常见的类型是缓冲区闩锁争用，具体表现为 wait_type 为 PAGELATCH_* 的闩锁的等待时间增加。 非缓冲区闩锁被归类于 LATCH* 等待类型。 如下图所示，应首先使用 sys.dm_os_wait_stats DMV 来查看系统累积等待时间，以确定缓冲区闩锁或非缓冲区闩锁导致的总等待时间所占的百分比。 如果遇到非缓冲区闩锁，则还必须检查 sys.dm_os_latch_stats DMV。

下图描述了 sys.dm_os_wait_stats 和 sys.dm_os_latch_stats DMV 返回的信息之间的关系。

有关 DMV 的详细信息 sys.dm_os_wait_stats ，请参阅 SQL Server 帮助中的 sys.dm_os_wait_stats 。

有关 DMV 的详细信息 sys.dm_os_latch_stats ，请参阅 SQL Server 帮助中的 sys.dm_os_latch_stats 。

以下闩锁等待时间指标表明，过度闩锁争用会影响应用程序性能：

• 平均页闩锁等待时间随着吞吐量的增加而持续增加：如果页闩锁平均等待时间随着吞吐量的增加而持续增加，并且缓冲区闩锁平均等待时间也增加到超出预期的磁盘响应时间，则应使用 sys.dm_os_waiting_tasks DMV 检查当前的等待任务。 如果单独分析平均值，可能会产生误导，因此在可能的情况下实时查看系统非常重要，以便了解工作负荷特征。 具体而言，检查任何页面上的 PAGELATCH_EX 和/或 PAGELATCH_SH 请求是否有高等待时间。 请按照以下步骤诊断随吞吐量增加而增加的页闩锁平均等待时间：

  • 使用示例脚本查询按会话 ID 排序的 sys.dm_os_waiting_tasks或计算一段时间内的等待数，以查看当前的等待任务并测量闩锁平均等待时间。

  • 使用示例脚本查询缓冲区描述符以确定导致闩锁争用的对象，以确定发生争用的索引和基础表。

  • 使用性能监视器计数器 MSSQL%InstanceName%\Wait Statistics\Page Latch Waits\Average Wait Time 或通过运行 sys.dm_os_wait_stats DMV 来测量页闩锁平均等待时间。

  注意

  若要计算特定等待类型的平均等待时间（按sys.dm_os_wait_stats原样wt_:type返回），将总等待时间（返回为）wait_time_ms除以等待任务数（返回为）。waiting_tasks_count

• 高峰负载期间闩锁等待类型上花费的总等待时间百分比：如果平均闩锁等待时间占应用程序负载的总体等待时间的百分比增加，则闩锁争用可能会影响性能，应进行调查。

  使用 SQL Server - Wait Statistics 对象性能计数器测量页闩锁等待和非页闩锁等待。 然后将这些性能计数器的值与 CPU、I/O、内存和网络吞吐量相关性能计数器的值进行比较。 例如，事务数/秒和批处理请求数/秒是衡量资源利用率的两个很好的指标。

  注意

  DMV 中不包括 sys.dm_os_wait_stats 每个等待类型的相对等待时间，因为此 DMW 度量自上次启动 SQL Server 实例或使用累积等待统计信息重置 DBCC SQLPERF以来的等待时间。 若要计算每种等待类型的相对等待时间，请对高峰负载之前和之后的 sys.dm_os_wait_stats 拍摄快照，然后计算差值。 示例脚本计算一段时间内的等待数可用于此目的。

  使用以下命令清除 DMV（仅适用于非生产环境sys.dm_os_wait_stats）：

  DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');
  

  可以运行类似的命令来清除 sys.dm_os_latch_stats DMV：

  DBCC SQLPERF ('sys.dm_os_latch_stats', 'CLEAR');
  

• 吞吐量不会增加，在某些情况下会随着应用程序负载的增加和 SQL Server 可用的 CPU 数的增加而减少：这在 闩锁争用示例中进行了说明。

• CPU 使用率不会随着应用程序工作负荷的增加而增加：如果系统上的 CPU 使用率没有随着应用程序吞吐量所推动的并发量的增加而增加，则这表明 SQL Server 正在等待某些条件，可能是闩锁争用的症状。

分析根本原因。 即使上述每个条件都属实，性能问题的根本原因仍然可能在于其他位置。 事实上，在大多数情况下，不理想的 CPU 使用率是由其他类型的等待引起的，例如锁定、I/O 相关的等待或与网络相关的问题。 根据经验，在进行更深入的分析之前，最好先解决占总等待时间最大比例的资源等待问题。

分析当前等待缓冲区闩锁

缓冲区闩锁争用表现为：wait_type 为 PAGELATCH_* 或 PAGEIOLATCH_* 的闩锁的等待时间增加，如 sys.dm_os_wait_stats DMV 中所示。 若要实时查看系统，请在系统上运行以下查询以加入 sys.dm_os_wait_stats、sys.dm_exec_sessions 和 sys.dm_exec_requests DMV。 结果可用于确定服务器上正在执行的会话的当前等待类型。

SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

此查询显示的统计信息如下：

以下查询返回所有非缓冲区闩锁的信息：

SELECT * FROM sys.dm_os_latch_stats
WHERE latch_class <> 'BUFFER'
ORDER BY wait_time_ms DESC;

此查询显示的统计信息如下：

此 DMV 返回的值是自上次重启数据库引擎或重置 DMV 以来的累积值。 使用 sqlserver_start_time 中的 列查找上次数据库引擎启动时间。 在运行了很长时间的系统上，这意味着某些统计信息（如 max_wait_time_ms）用处不大。 可以使用以下命令重置此 DMV 的等待统计信息：

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

SQL Server 闩锁争用场景

观察到以下场景会导致过度闩锁争用。

最后一页/尾页插入争用

一种常见的 OLTP 做法是在标识或日期列上创建聚集索引。 这有助于维护索引的良好物理组织，从而极大地提高对索引的读写性能。 然而，这种架构设计可能会无意中导致闩锁争用。 此问题最常见于包含小行的大型表，并将包含顺序递增的前导键列（如升序整数或日期时间键）插入到索引中。 在这种情况下，应用程序很少（如果有的话）执行更新或删除操作，但存档操作除外。

在下面的示例中，线程 1 和线程 2 都希望执行将存储在 299 页上的记录的插入操作。 从逻辑锁定的角度来看，没有问题，因为使用的是行级锁，可以同时持有同一页上两条记录的排他锁。 但是，为了确保物理内存的完整性，一次只有一个线程能获取排他闩锁；因此，对页面的访问是序列化的，以防止内存中丢失更新。 在这种情况下，线程 1 获取排他闩锁，而线程 2 处于等待状态，这会在等待统计信息中为此资源注册一次 PAGELATCH_EX 等待。 这通过 wait_type DMV 中的 sys.dm_os_waiting_tasks 值显示。

该争用通常称为“最后一页插入”争用，因为它发生在 B 树的最右侧边缘，如下图所示：

此类闩锁争用可以解释如下。 将新行插入索引时，SQL Server 使用以下算法执行修改：

1. 遍历 B 树，以找到保存新记录的正确页面。

2. 用 PAGELATCH_EX 锁定页面，防止其他人修改页面，并在所有非叶页面上获取共享闩锁 (PAGELATCH_SH)。

   注意

   在某些情况下，SQL 引擎也需要在非叶 B 树页上获取 EX 闩锁。 例如，当发生页分裂时，任何直接受影响的页面都需要被独占锁定（PAGELATCH_EX）。

3. 记录该行已被修改的日志条目。

4. 将该行添加到页并将页标记为“脏”。

5. 解锁所有页。

如果表索引基于按顺序递增的键，则每个新插入都会转到 B 树末尾的同一页，直到该页已满。 在高并发场景中，这可能会导致 B 树最右侧的资源争用，并可能同时发生在聚集索引和非聚集索引上。 受此类争用影响的表主要接受 INSERT 查询，而有问题索引的页通常相对密集（例如，行大小约为 165 字节（包括行开销）等于约 49 行/页）。 在这个插入操作密集的示例中，我们预期 PAGELATCH_EX/PAGELATCH_SH 等待会发生，这是典型的观察结果。 若要检查页闩锁等待与树页闩锁等待，请使用 sys.dm_db_index_operational_stats DMV。

下表总结了在这种闩锁争用中观察到的主要因素：

具有非聚集索引和随机插入的小型表（队列表）上的闩锁争用

当 SQL 表用作临时队列（例如，在异步消息传递系统中），通常会看到这种情况。

在此方案中，在以下条件下可能会发生排他 (EX) 和共享 (SH) 闩锁争用：

• 在高并发性下执行插入、查询、更新或删除操作。
• 行大小相对较小（导致页密集）。
• 表中的行数相对较少；导致由具有两个或三个索引深度定义的浅 B 树。

即使在 B 树中进行随机访问，也可能发生闩锁争用，例如当非连续列是聚集索引中的前导键时。 以下屏幕截图来自一个遇到此类闩锁争用的系统。 在本例中，争用是由于较小的行大小和相对较浅的 B 树造成的页密度。 随着并发性的增加，即使在 B 树中进行随机插入，页上也会发生闩锁争用，因为 GUID 是索引中的前导列。

在下面的屏幕截图中，等待发生在缓冲区数据页和页可用空间 (PFS) 页上。 即使数据文件的数量增加，缓冲区数据页上的闩锁争用也很普遍。

下表总结了在这种闩锁争用中观察到的主要因素：

浅 B 树与跨索引随机插入的组合很容易导致 B 树中发生页拆分。 若要执行页面拆分，SQL Server 必须在所有级别获取共享（SH）闩锁，然后获取 B 树中涉及的页面的排他（EX）闩锁。 此外，当并发性较高且数据不断插入和删除时，可能会发生 B 树根拆分。 在这种情况下，其他插入可能需要等待在 B 树上获取的任何非缓冲区闩锁。 这表现为在 ACCESS_METHODS_HOBT_VIRTUAL_ROOT DMV 中观察到的 sys.dm_os_latch_stats 闩锁类型上的大量等待。

可以修改以下脚本，以确定受影响表上索引的 B 树深度。

SELECT
    o.name AS [table],
    i.name AS [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth') + indexProperty(object_id(o.name), i.name, 'isClustered') AS depth, --clustered index depth reported doesn't count leaf level
    i.[rows] AS [rows],
    i.origFillFactor AS [fillFactor],
    CASE (indexProperty(object_id(o.name), i.name, 'isClustered'))
        WHEN 1 THEN 'clustered'
        WHEN 0 THEN 'nonclustered'
        ELSE 'statistic'
    END AS type
FROM sysIndexes AS i
     INNER JOIN sysObjects AS o
         ON o.id = i.id
WHERE o.type = 'u'
      AND indexProperty(object_id(o.name), i.name, 'isHypothetical') = 0 --filter out hypothetical indexes
      AND indexProperty(object_id(o.name), i.name, 'isStatistics') = 0 --filter out statistics
ORDER BY o.name;

页可用空间 (PFS) 页上的闩锁争用

PFS 代表页可用空间，SQL Server 为每个数据库文件中的每 8088 页（从PageID = 1开始）分配一个 PFS 页。 PFS 页中的每个字节都会记录信息，包括页面上的可用空间量（如果已分配，以及页面是否存储虚影记录）。 PFS 页包含当插入或更新操作需要新页时可分配的页的相关信息。 在多种情况下，包括发生任何分配或解除分配时，必须更新 PFS 页面。 由于需要使用更新 (UP) 闩锁来保护 PFS 页，因此如果文件组中的数据文件相对较少，而 CPU 内核数很多时，就会在 PFS 页上发生闩锁争用。 解决此问题的一个简单方法是增加每个文件组的文件数。

如果在 PAGELATCH_UP 中观察到 PFS 或 SGAM 页面有许多 tempdb 等待，请完成以下步骤以消除此瓶颈：

1. 添加数据文件 tempdb ，使 tempdb 数据文件的数量等于服务器中的处理器核心数。

2. 启用 SQL Server 跟踪标志 1118。

有关系统页面上争用导致的分配瓶颈的详细信息，请参阅博客文章 什么是分配瓶颈？

tempdb 上的表值函数和闩锁争用

除了分配争用之外，还有其他因素可能导致 tempdb 上的闩锁争用，例如查询中大量使用 TVF。

处理不同表格模式的闩锁争用

以下部分介绍了可用于解决或处理与过度闩锁争用相关的性能问题的技术。

使用非连续的前导索引键

处理闩锁争用的一个方法是将有序索引键替换为无序键，以便在索引范围内均匀分配插入内容。

通常，这是通过在索引中有一个可以按比例分配工作负载的主导列来完成的。 你有两个选择：

选项：使用表中的列在索引键范围内分布值

评估工作负荷，以获得可用于在整个键范围内分配插入的自然值。 例如，考虑一个 ATM 银行场景，其中 ATM_ID 可能是将插入分发到事务表中进行提款的合适选择，因为一个客户一次只能使用一台 ATM。 同样，在销售点系统中，也许 Checkout_ID 或应用商店 ID 是一个自然值，可用于在键范围内分配插入。 此方法需要创建一个复合索引键，其中前导键列是标识的列的值，或者该值的某些哈希与一个或多个额外列相结合以提供唯一性。 在大多数情况下，值的哈希效果最佳，因为过多的不同值会导致物理存储效果不佳。 例如，在销售点系统中，可以根据 Store ID 创建哈希作为模数，该模数与 CPU 内核数一致。 此方法会导致表中的范围相对较少，但是以这种方式分发插入以避免闩锁争用就足够了。 下图演示了此方法。

此模式是在性能实验室参与期间实施的，它解决了具有 32 个物理 CPU 内核的系统上的闩锁争用问题。 该表用于存储事务结束时的结账余额；每个业务事务对表执行一次插入。

原始表定义

使用原始表定义时，在聚集索引 pk_table1 上观察到过度闩锁争用：

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (TransactionID, UserID);
GO

重新排序的索引定义

以 UserID 作为主键中的前导列，对索引的关键列进行重新排序，在页面上提供了几乎随机的插入分布。 最终的分布不是 100% 随机的，因为并非所有用户都同时在线，但分布是随机的，足以缓解过度闩锁争用。 重新排序索引定义的一个注意问题是，必须修改针对此表的任何选择查询，以同时使用UserID和TransactionID作为相等谓词。

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (UserID, TransactionID);
GO

使用哈希值作为主键中的前导列

下表定义可用于生成一个与 CPU 数量对齐的模数。该模数是使用按顺序递增的值HashValue生成，以确保在 B 树中均匀分布TransactionID。

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

-- Consider using bulk loading techniques to speed it up
ALTER TABLE table1
    ADD [HashValue] AS (CONVERT (TINYINT, ABS([TransactionID]) % (32))) PERSISTED NOT NULL;

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (HashValue, TransactionID, UserID);
GO

选项：使用 GUID 作为索引的前导键列

如果没有自然分隔符，则可以将 GUID 列用作索引的前导键列，以确保插入的统一分布。 虽然在索引键方法中使用 GUID 作为前导列可以将分区用于其他功能，但这种方法也有可能带来更多页拆分、不良物理组织和低页密度等潜在弊端。

对计算列使用哈希分区

SQL Server 中的表分区可用于减轻过度闩锁争用。 在分区表上使用计算列创建哈希分区方案是一种常见的方法，可以通过以下步骤完成：

1. 建新的文件组或使用现有的文件组来保存分区。

2. 如果使用新的文件组，请在 LUN 上均衡地平衡单个文件，并注意使用最佳布局。 如果访问模式涉及较高的插入率，请确保创建的文件数量与 SQL Server 计算机上的物理 CPU 核数量相同。

3. CREATE PARTITION FUNCTION使用命令将表分区为 X 分区，其中 X 是 SQL Server 计算机上的物理 CPU 核心数。 （至少 32 个分区）

   注意

   分区数与 CPU 核心数的 1：1 对齐并非始终是必需的。 在许多情况下，此值可能小于 CPU 核心数。 分区越多，查询必须搜索所有分区时会产生更多的开销。在这种情况下，减少分区数量可能会有所帮助。 在针对具有实际客户工作负荷的 64 和 128 个逻辑 CPU 系统的 SQLCAT 测试中，32 个分区就足以解决过度闩锁争用问题并达到扩展目标。 最终，理想的分区数应通过测试确定。

4. 使用 CREATE PARTITION SCHEME 命令：

   • 将分区函数绑定到文件组。
   • 向表中添加 tinyint 或 smallint 类型的哈希列。
   • 计算良好的哈希分布。 例如，将 HASHBYTES 与 modulo 或 BINARY_CHECKSUM 结合使用。

以下示例脚本可以根据实现进行自定义：

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

该脚本可用于对因最后一页/尾页插入争用引起的问题的表进行哈希分区。 此方法通过对表进行分区，并使用哈希值取模运算在表分区之间分布插入，消除了最后一页的争用。

对计算列使用哈希分区有什么作用

如下图所示，此方法通过在哈希函数上重新生成索引，并创建与 SQL Server 计算机上的物理 CPU 内核相同数量的分区，消除了最后一页的争用。 插入项仍将进入逻辑范围的末尾（按顺序递增的值），但哈希值取模运算确保了插入在不同的 B 树上拆分，从而缓解了瓶颈。 下面的图表中阐释了这一点：

使用哈希分区时的权衡

虽然哈希分区可以消除插入争用，但在决定是否使用此方法时，需要权衡以下几点：

• 在大多数情况下，需要修改选择查询以将哈希分区包含在谓词中，并导致在发出这些查询时不会提供分区消除的查询计划。 下面的屏幕截图显示了一个在实现哈希分区之后没有消除分区的糟糕计划。

  

• 它消除了在某些其他查询（如基于范围的报告）上消除分区的可能性。

• 将哈希分区表联接到另一个表时，若要实现分区消除，需要在同一键上对第二个表进行哈希分区，哈希键应是联接条件的一部分。

• 哈希分区阻止了将分区用于其他管理功能，如滑动窗口存档和分区切换功能。

哈希分区是一种有效的策略，可以减轻过度闩锁争用，因为它通过减轻插入争用来提高整体系统吞吐量。 由于涉及一些权衡，因此它可能不是某些访问模式的最佳解决方案。

用于解决闩锁争用的方法摘要

以下两节总结了可用于解决过度闩锁争用的方法：

非顺序键/索引

优点：

• 允许使用其他分区功能，例如使用滑动窗口方案和分区切换功能来存档数据。

缺点：

• 在选择键/索引以确保插入始终“足够接近”均匀分布时可能遇到的挑战。
• GUID 作为前导列可用于保证均匀分布；但需要注意的是，它可能会导致过度页拆分操作。
• 跨 B 树的随机插入可能会导致页拆分操作过多，并导致非叶页上发生闩锁争用。

对计算列使用哈希分区

优点：

• 插入透明。

缺点：

• 分区不能用于预期管理功能，例如使用分区切换选项存档数据。
• 可能导致查询的分区消除问题，包括单独的和基于范围的选择/更新，以及执行连接的查询。
• 添加持久化计算列是一项脱机操作。

演练：诊断闩锁争用

以下演练演示了诊断 SQL Server 闩锁争用和处理不同表模式的闩锁争用中所述的工具和方法，以解决现实场景中的问题。 此场景描述了一个客户参与的一项负载测试活动，旨在对销售系统进行测试。该测试模拟了约 8,000 家商店在一个配备 8 个插槽、32 个物理核心和 256 GB 内存的系统上运行的 SQL Server 应用程序中执行事务。

下图详细说明了用于测试销售点系统的硬件：

症状：热闩锁

在这种情况下，我们观察到 PAGELATCH_EX 等待时间很长，而我们通常将高等待时间定义为平均大于 1 毫秒。 在本例中，我们观察到等待时间一直超过 20 毫秒。

一旦我们确定闩锁争用是有问题的，我们就开始确定是什么导致了闩锁争用。

隔离导致闩锁争用的对象

以下脚本使用 resource_description 列来隔离导致 PAGELATCH_EX 争用的索引：

SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       s.name AS schema_name,
       o.name AS object_name,
       i.name AS index_name
FROM sys.dm_os_buffer_descriptors AS bd
     INNER JOIN (SELECT *,
             --resource_description
             CHARINDEX(':', resource_description) AS file_index,
             CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) AS page_index,
             resource_description AS rd
      FROM sys.dm_os_waiting_tasks AS wt
      WHERE wait_type LIKE 'PAGELATCH%') AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
        AND bd.file_id = SUBSTRING(wt.rd, wt.file_index + 1, 1) --wt.page_index)
        AND bd.page_id = SUBSTRING(wt.rd, wt.page_index + 1, LEN(wt.rd))
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = au.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id
ORDER BY wt.wait_duration_ms DESC;

如图所示，争用发生在表 LATCHTEST 和索引名称 CIX_LATCHTEST 上。 请注意，名称已更改，以匿名化工作负荷。

有关重复轮询并使用临时表确定可配置时间段内总等待时间的更高级脚本，请参阅附录中的查询缓冲区描述符以确定导致闩锁争用的对象。

隔离导致闩锁争用的对象的替代方法

有时查询 sys.dm_os_buffer_descriptors 可能不切实际。 随着系统内存以及缓冲池可用内存的增加，运行此 DMV 所需的时间也会增加。 在 256 GB 系统上，运行此 DMV 可能需要长达 10 分钟或更长的时间。 另一种方法是可用的，大致概述如下，并用我们在实验室中运行的不同工作负荷进行了说明：

1. 使用附录脚本查询按等待持续时间排序的 sys.dm_os_waiting_tasks，查询当前的等待任务。

2. 标识观察到保护的关键页面，当多个线程在同一页上争用时会发生这种情况。 在此示例中，执行插入的线程在 B 树的末尾页上竞争，并等待，直到它们可以获得 EX 闩锁。 在第一个查询中，resource_description 指示了这一点，在我们的例子中是 8:1:111305。

3. 启用跟踪标志 3604，通过 DBCC PAGE 替换括号中的值为从 resource_description 中获取的值，使用以下语法公开有关页面的详细信息：

   启用跟踪标志 3604 以启用控制台输出：

   DBCC TRACEON (3604);
   

   检查页面的详细信息：

   DBCC PAGE (8, 1, 111305, -1);
   

4. 检查 DBCC 输出。 应该有一个关联的元数据 ObjectID，在本例中是 78623323。

   

5. 现在，我们可以运行以下命令来确定导致争用的对象的名称，如预期所示 LATCHTEST。

   注意

   请确保位于正确的数据库上下文中，否则查询返回 NULL。

   --get object name
   SELECT OBJECT_NAME(78623323);
   

   

摘要和结果

通过使用上述方法，我们可以确认争用发生在表中具有按顺序递增的键值的聚集索引上，该表到目前为止收到的插入次数最多。 对于具有按顺序递增键值的索引，例如日期时间、标识或应用程序生成的TransactionID，此类争用并不少见。

为了解决此问题，我们使用了对计算列使用哈希分区，并观察到性能提升了 690%。 下表总结了使用计算列实现哈希分区前后应用程序的性能。 消除闩锁争用瓶颈后，和我们预期的一样，CPU 利用率的提升与吞吐量的提升基本一致：

如上表中所示，正确识别和解决因页面闩锁争用过多而导致的性能问题可能会对整体应用程序性能产生积极的影响。

附录：备用技术

避免过度页闩锁争用的一种可能策略是用 char 列填充行，以确保每行使用一个完整的页面。 当整体数据量较小，并且需要解决由以下因素组合引起的 EX 页闩锁争用时，此策略是一种选择：

• 行大小较小
• 浅 B 树
• 具有高速随机插入、选择、更新和删除操作的访问模式
• 小型表，例如临时队列表

通过填充行以占据整个页面，需要 SQL 分配更多页面，使更多页面可用于插入，并减少 EX 页闩锁争用。

填充行以确保每一行占据一整页

类似于以下的脚本可用于填充行以占据整个页面：

ALTER TABLE mytable ADD Padding CHAR(5000) NOT NULL DEFAULT ('X');

为了完整起见，对这种方法进行了解释；在实践中，SQLCAT 仅在单个性能服务中包含 10,000 行的小型表上使用此方法。 此方法的应用范围有限，因为它增加了 SQL Server 上大型表的内存压力，并可能导致非叶页上的非缓冲区闩锁争用。 额外的内存压力可能是此技术应用的重要限制因素。 随着新式服务器中可用内存量的增加，OLTP 工作负荷的大部分工作集通常都保存在内存中。 当数据集增加到内存无法容纳的大小时，性能就会显著下降。 因此，此方法仅适用于小型表。 SQLCAT 不将此方法用于大型表的最后一页/尾随页插入争用等场景。

附录：SQL Server 闩锁争用脚本

本部分包含可用于帮助诊断和排查闩锁争用问题的脚本。

查询按会话 ID 排序的 sys.dm_os_waiting_tasks

以下示例脚本查询 sys.dm_os_waiting_tasks，并返回按会话 ID 排序的闩锁等待：

-- WAITING TASKS ordered by session_id
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY session_id;

查询按等待持续时间排序的 sys.dm_os_waiting_tasks

以下示例脚本查询 sys.dm_os_waiting_tasks，并返回按等待持续时间排序的闩锁等待：

-- WAITING TASKS ordered by wait_duration_ms
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

计算一段时间内的等待数

以下脚本计算并返回一段时间内的闩锁等待数。

/* Snapshot the current wait stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb.  if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time AS DATETIME;
DECLARE @previous_snap_time AS DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (SELECT name
               FROM tempdb.sys.sysobjects
               WHERE name LIKE '#_wait_stats%')
    CREATE TABLE #_wait_stats
    (
        wait_type VARCHAR (128),
        waiting_tasks_count BIGINT,
        wait_time_ms BIGINT,
        avg_wait_time_ms INT,
        max_wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        avg_signal_wait_time INT,
        snap_time DATETIME
    );

INSERT INTO #_wait_stats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, snap_time)
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms,
       signal_wait_time_ms,
       getdate()
FROM sys.dm_os_wait_stats;

--get the previous collection point
SELECT TOP 1 @previous_snap_time = snap_time
FROM #_wait_stats
WHERE snap_time < (SELECT MAX(snap_time)
                   FROM #_wait_stats)
ORDER BY snap_time DESC;

--get delta in the wait stats
SELECT TOP 10 s.wait_type,
              (e.waiting_tasks_count - s.waiting_tasks_count) AS [waiting_tasks_count],
              (e.wait_time_ms - s.wait_time_ms) AS [wait_time_ms],
              (e.wait_time_ms - s.wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_wait_time_ms],
              (e.max_wait_time_ms) AS [max_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) AS [signal_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_signal_time_ms],
              s.snap_time AS [start_time],
              e.snap_time AS [end_time],
              DATEDIFF(ss, s.snap_time, e.snap_time) AS [seconds_in_sample]
FROM #_wait_stats AS e
     INNER JOIN (SELECT *
      FROM #_wait_stats
      WHERE snap_time = @previous_snap_time) AS s
     ON (s.wait_type = e.wait_type)
WHERE e.snap_time = @current_snap_time
      AND s.snap_time = @previous_snap_time
      AND e.wait_time_ms > 0
      AND (e.waiting_tasks_count - s.waiting_tasks_count) > 0
      AND e.wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'SOS_SCHEDULER_YIELD',
                              'DBMIRRORING_CMD', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
                              'BROKER_RECEIVE_WAITFOR', 'WAITFOR', 'SLEEP_TASK',
                              'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TO_FLUSH',
                              'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY (e.wait_time_ms - s.wait_time_ms) DESC;

--clean up table
DELETE FROM #_wait_stats
WHERE snap_time = @previous_snap_time;

查询缓冲区描述符以确定导致闩锁争用的对象

以下脚本查询缓冲区描述符，以确定哪些对象与最长闩锁等待时间关联。

IF EXISTS (SELECT *
           FROM tempdb.sys.objects
           WHERE [name] LIKE '#WaitResources%')
    DROP TABLE #WaitResources;

CREATE TABLE #WaitResources
(
    session_id INT,
    wait_type NVARCHAR (1000),
    wait_duration_ms INT,
    resource_description sysname NULL,
    db_name NVARCHAR (1000),
    schema_name NVARCHAR (1000),
    object_name NVARCHAR (1000),
    index_name NVARCHAR (1000)
);
GO

DECLARE @WaitDelay AS VARCHAR (16), @Counter AS INT, @MaxCount AS INT, @Counter2 AS INT;
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'; -- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
   INSERT INTO #WaitResources (session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
   SELECT wt.session_id,
         wt.wait_type,
         wt.wait_duration_ms,
         wt.resource_description
   FROM sys.dm_os_waiting_tasks AS wt
   WHERE wt.wait_type LIKE 'PAGELATCH%'
         AND wt.session_id <> @@SPID;

   -- SELECT * FROM sys.dm_os_buffer_descriptors;

   SET @Counter = @Counter + 1;
   WAITFOR DELAY @WaitDelay;
END

--SELECT * FROM #WaitResources;

UPDATE #WaitResources
    SET db_name = DB_NAME(bd.database_id),
        schema_name = s.name,
        object_name = o.name,
        index_name = i.name
FROM #WaitResources AS wt
     INNER JOIN sys.dm_os_buffer_descriptors AS bd
         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
        AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) - CHARINDEX(':', wt.resource_description) - 1)
        AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) + 1, LEN(wt.resource_description) + 1)
        -- AND wt.file_index > 0 AND wt.page_index > 0
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = AU.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id;

SELECT * FROM #WaitResources
ORDER BY wait_duration_ms DESC;
GO
/*
--Other views of the same information
SELECT wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY wait_type, db_name, schema_name, object_name, index_name;
SELECT session_id, wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY session_id, wait_type, db_name, schema_name, object_name, index_name;
*/

--SELECT * FROM #WaitResources
--DROP TABLE #WaitResources;

哈希分区脚本

对计算列使用哈希分区中介绍了此脚本的用法，应根据实现进行自定义。

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

相关内容

• 性能监视和优化工具