更改跟踪導致閂鎖爭用

我正在使用 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 毫秒。

當我檢查正在執行的查詢時，我只能看到同步查詢，全部被阻止，帶有“PAGELATCH_UP”，所有嘗試訪問更改跟踪表，超過 300 個查詢被阻止。

我有幾個問題：

• SQL Server 在查找更改跟踪更改時是否會鎖定整個表？
• 使用 SQL Entreprise 會有更好的結果還是不會改變任何東西？
• 知道為什麼更改跟踪在大多數情況下都能正常工作，但每週都會在沒有明顯原因的情況下崩潰嗎？

這些是我的更改跟踪表大小。我的查詢阻塞的表是前三個表，只有幾 mb 的數據。

他們都在等待同一個waitresource。

Waitresource 2:4:88968 在 tempdb 中。但是 tempdb 只負責大約 9% 的伺服器寫入和 6% 的讀取。

但是我的查詢不使用 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);

我的伺服器再次恢復正常狀態，所有數百個被阻止的查詢都消失了。我猜那是參數嗅探？希望不要再發生了。想找個辦法讓它不再發生。

我不確定如何處理糟糕的計劃問題，也許有人會提供更好的查詢調整技能來幫助解決這個問題。

但是，談到 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數據頁越多，數據文件越多，分配分佈就越好。

因此，即使採用“糟糕的計劃”，您也希望減少閂鎖爭用，從而減慢速度。

引用自：https://dba.stackexchange.com/questions/257970