主页 / dba / 问题 / 257970
Accepted
Danielle Paquette-Harvey
Asked: 2020-01-23 07:50:58 +0800 CST

更改跟踪导致闩锁争用

  • 772

我正在使用 Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) Jun 15 2019 23:15:58 版权所有 (c) Microsoft Corporation Standard Edition (64-bit) o​​n Windows Server 2012 R2 Standard 6.3 （构建 9600：）

该数据库的大小约为 870 GB。它是 SQL 标准，我在服务器上有 128 GB 的 RAM。该数据库位于 SSD 驱动器上。数据文件与日志文件位于不同的驱动器上，并且 Tempdb 也有自己的 SSD 驱动器。服务器平均每秒大约 1200 个查询，它可以高达 2000 个查询/秒。重新编译保持在较低水平，每秒只有 1 到 8 次。页面预期寿命不错，平均为 61 分钟。

服务器有 6 个物理核心 + 超线程。

我们在一个有数千台设备连接并尝试使用跟踪键同步更改的系统上大量使用 SQL Server 的更改跟踪。

它通常运行良好，但随后，服务器的锁存器会不时地飙升，从 0 毫秒到平均 60677 毫秒。

SQL 锁存器

当我检查正在运行的查询时，我只能看到同步查询，全部被阻止，带有“PAGELATCH_UP”，所有尝试访问更改跟踪表，超过 300 个查询被阻止。

我有几个问题：

  • SQL Server 在查找更改跟踪更改时是否会锁定整个表？
  • 使用 SQL Entreprise 会有更好的结果还是不会改变任何东西？
  • 知道为什么更改跟踪在大多数情况下都能正常工作，但每周都会在没有明显原因的情况下崩溃吗？

这些是我的更改跟踪表大小。我的查询阻塞的表是前三个表，只有几 mb 的数据。

在此处输入图像描述

他们都在等待同一个waitresource。

等待资源

Waitresource 2:4:88968 在 tempdb 中。但是 tempdb 只负责大约 9% 的服务器写入和 6% 的读取。

io报告

但是我的查询不使用 tempdb，所以我猜是因为内部更改跟踪的工作方式？这是我的查询

DECLARE @Id INT; SET @Id = (SELECT Id FROM Users WHERE No=@No); 

SELECT DISTINCT lh.Key1 
FROM ( 
    SELECT Key1 FROM CHANGETABLE(CHANGES dbo.Table1, @TrackingKey) AS CT 
    UNION ALL 
    SELECT Key1 
    FROM dbo.Table2 lhd 
    INNER JOIN (SELECT Key2 FROM CHANGETABLE(CHANGES dbo.Table2, @TrackingKey) AS CT) AS CTLHD ON(CTLHD.Key2=lhd.Key2) 
    UNION ALL 
    SELECT Key1 
    FROM CHANGETABLE(CHANGES dbo.Table3, @TrackingKey) AS CT 
) AS L 
JOIN dbo.Table1 lh ON lh.Key1 = L.Key1 
WHERE lh.Id = @Id AND lh.Date BETWEEN @StartUtc AND @EndUtc

我的 tempdb 有 10 个文件，它们的大小相同。

临时数据库

我通常最终使客户端恢复正常的做法是将其置于停机时间，然后逐渐将其恢复，以便所有移动设备逐渐同步。但是我们的系统是关键任务，这不是一个长期的解决方案。

我一直在考虑的另一个解决方案是改变系统处理更改跟踪查询的方式。让移动设备与“自制”表同步，并用来自更改跟踪的单个服务读取更改填充此表。这样，我会将并发查询限制在更改跟踪表中，但恐怕我只会将问题转移到自制表中。

对此有什么想法吗？任何帮助将不胜感激。

编辑：头部阻滞剂

我试图确定谁是拦路者以及它在等待什么，但这是一项艰巨的任务。看来我有很多“头脑障碍者”。

所有查询都运行相同的 SELECT，几乎都分成 4 个线程，对于某些查询，它们根本没有被阻塞，而是在等待“MISCELLANEOUS”，但对于某些查询，至少部分线程被其他查询阻塞。

例如，现在有 294 个线程正在显示。

查询 202 被分成 4 个线程，其中一个线程被 123 阻塞，但其他线程没有被阻塞。三个线程正在等待“MISCELLANEOUS”，阻塞线程正在等待“PAGELATCH_UP”

至于查询 123，它没有被阻塞，它有 4 个线程正在等待“MISCELLANEOUS”

或者例如，查询 219 在一个线程上被查询 140 阻塞，在其他三个线程上被 69 阻塞。

69 被 193 阻​​止，193 正在运行，再次等待“杂项”。140 不再在列表中，因此它要么超时要么已完成。

我的并行成本阈值为 70。
锁定 0
最大并行度 3
查询等待 -1

未在数据库上启用快照隔离级别。查询未使用快照隔离级别。

数据库选项

我还检查了表的统计信息，甚至 sys.change_tracking 表。对于查询的表，表上的索引没有碎片化（小于 10%）。

我运行了一个或两个查询，一般查询的结果是 4 行，由于 DISTINCT 子句而变成只有一行。所以它不像返回数千行。

当我在 SSMS 中运行查询时，它速度很快并且不会阻塞，即使我目前在同一查询的服务器上看到数百个被阻塞的查询。所以我想这可能与参数嗅探有关？

这是我在 SSMS 中执行时的 I/O 统计信息。

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 92 ms, elapsed time = 92 ms.
Table 'Users'. Scan count 0, logical reads 2, physical reads 0, read-ahead  reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscommittab'. Scan count 3, logical reads 555, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'change_tracking_62623266'. Scan count 1, logical reads 3364, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table1'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table2'. Scan count 0, logical reads 34281, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'change_tracking_46623209'. Scan count 1, logical reads 1152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'change_tracking_78623323'. Scan count 1, logical reads 1077, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 296 ms,  elapsed time = 435 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Completion time: 2020-01-22T14:18:25.9480651-05:00

这是我的查询计划 https://www.brentozar.com/pastetheplan/?id=By7k-7UWI

但同样，这是不阻止的版本。我启用了查询存储，所以我可以尝试获取阻止的版本，我只是不是 100% 如何做到这一点。

已编辑：查询商店信息

我的查询是查询存储的“回归查询”中的第一个。

查询店铺信息

计划

根据查询商店，这里是“坏计划” https://www.brentozar.com/pastetheplan/?id=ryqKGQUbL

这是“好计划” https://www.brentozar.com/pastetheplan/?id=rknnGQ8WL

我应该“强迫”这个好计划吗？

编辑我的解决方案

好的，所以我使用了 Brent Ozar 的 sp_blitzcache ( https://www.brentozar.com/blitz/ )，“expert_mode”为 1，以便能够检索“坏计划”的句柄并能够从缓存（不清除任何其他内容）。

DBCC FREEPROCCACHE (0x06000800155A5106F08F632F1C00000001000000000000000000000000000000000000000000000000000000);

我的服务器再次恢复正常状态，所有数百个被阻止的查询都消失了。我猜那是参数嗅探？希望不要再发生了。想找个办法让它不再发生。

sql-server-2016 change-tracking

1 个回答

  1. Best Answer

    我不确定如何处理糟糕的计划问题，也许有人会提供更好的查询调优技能来帮助解决这个问题。

    但是，谈到 tempdb 争用问题，所有这些会话都在争夺的页面是 PFS 页面。这些在文档中定义为：

    Page Free Space (PFS) 页面记录每个页面的分配状态，是否已分配单个页面，以及每个页面上的可用空间量。

    请注意，它们仅跟踪某些类型页面的可用空间：

    页面中的可用空间量仅为堆和文本/图像页面维护。

    那里的“文本/图像”注释包括更现代nvarchar(max)的 LOB 数据类型（等等）。

    顺便说一句，你可以说它是一个 PFS 页面，因为它可以被 8088 整除：

    因此，在第一个 PFS 页之后有一个新的 PFS 页 8,088 页，并且在随后的 8,088 页间隔中有额外的 PFS 页。

    所有这一切都是说“坏计划”可能会溢出到 tempdb（有两种排序和一种哈希连接），这会导致对该特定 PFS 页面的争用。还有一些急切的索引假脱机正在写入 tempdb。

    您可以通过安装包含此修复程序的SP2 CU1来缓解此问题：

    除了跨所有 tempdb 数据文件的循环分配之外，此修复还通过在同一数据文件中的多个 PFS 页面上执行循环分配来改进 PFS 页面分配。因此，一个数据文件中包含的PFS数据页越多，数据文件越多，分配分布就越好。

    因此，即使采用“糟糕的计划”，您也希望减少闩锁争用，从而减慢速度。

    • 4

相关问题