診斷與解決 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_EX） 或共用 （PAGEIOLATCH_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 閂鎖與 DT 閂鎖不相容，因此它可以防止任何其他執行緒破壞參照結構。 例如，KP 鎖存器可防止其參考的結構被懶寫進程終結。 如需有關如何將惰性寫入器過程與 SQL Server 緩衝區頁面管理一起使用的詳細資訊，請參閱 在資料庫引擎中寫入頁面。

• SH：共用閂鎖。 必須讀取參考的結構（例如，讀取資料頁）。 多個執行緒可以同時存取資源，以在共用閂鎖下讀取。

• UP：更新鎖存器。 與 SH（共用閂鎖）和 KP 相容，但不與其他閂鎖相容，因此不允許 EX 閂鎖寫入參考的結構。

• EX：專用閂鎖。 封鎖其他執行緒寫入或讀取參考的結構。 其中一個使用範例，就是修改頁面的內容，以進行損毀頁保護。

• DT：銷毀鎖扣。 必須在銷毀參考結構的內容之前取得。 例如，延遲寫入器進程必須取得 DT 閂鎖，才能釋放乾淨的頁面，才能將它新增至可供其他執行緒使用的可用緩衝區清單。

閂鎖模式具有不同的相容性層級，例如，共用閂鎖（SH）與更新（UP）或保留（KP）閂鎖相容，但與銷毀（DT）閂鎖不相容。 只要閂鎖彼此相容，就可以在相同的結構上同時取得多個閂鎖。 當執行線嘗試取得以不相容模式保留的閂鎖時，它會放入佇列中，以等候指出資源可用的訊號。 SOS_Task 類型的執行緒同步鎖定是透過實施對佇列的序列化存取，用以保護等候佇列。 您必須取得此執行緒同步鎖定，才能將項目新增至佇列。 當釋放不相容的閂鎖時，SOS_Task 執行緒同步鎖定也會通知佇列中的執行緒，讓等候中的執行緒取得相容的閂鎖並繼續工作。 在釋放閂鎖要求時，會依先進先出 (FIFO) 的順序處理等候佇列。 閂鎖遵循此 FIFO 系統以確保公平，並防止耗盡執行緒。

下表列出閂鎖模式相容性 （是 表示相容性， 否 表示不相容）：

SQL Server 超級閂鎖和子閂鎖

隨著基於 NUMA 的多插槽/多核心系統日益普及，SQL Server 2005 推出了超級閂鎖，也稱為子閂鎖，它僅在具有 32 個或更多邏輯處理器的系統上有效。 超接點鎖可以提高 SQL 引擎在高度並行的 OLTP 工作負載中，對於特定使用模式的效率，例如，當某些頁面有大量唯讀共用存取（SH）的模式，且這些頁面很少被寫入時。 具有此類存取模式的頁面範例是 B 樹 （即索引） 根頁面;SQL 引擎要求在 B 樹狀結構中的任何層級發生分頁時，在根頁面上保留共用閂鎖。 在插入繁重且高並行的 OLTP 工作負載中，分頁數目會隨著輸送量而大幅增加，這可能會降低效能。 超級閂鎖可以提高存取共用頁面的效能，其中多個並行運行的工作執行緒需要 SH 閂鎖。 為了達成此目的，SQL Server 引擎會動態地將這類頁面上的閂鎖升級為超級閂鎖。 超級閂鎖會將單一閂鎖分割成子閂鎖結構陣列，每個 CPU 核心每個分割區一個子閂鎖，因此主要閂鎖會變成 Proxy 重新導向器，而且唯讀閂鎖不需要全域狀態同步處理。 如此一來，一律指派給特定 CPU 的工作者只需要取得指派給本機排程器的共用 （SH） 子閂鎖。

取得相容閂鎖 (例如共用 Superlatch) 使用資源較少，而且比非分割共用閂鎖更能調整對熱門頁面的存取，因為只要存取本機 NUMA 記憶體，就能移除全域狀態同步處理需求，大幅改善效能。 相反地，取得專屬的 (EX) 超級閂鎖比取得 EX 一般閂鎖更昂貴，因為 SQL 必須跨所有子閂鎖發出訊號。 當觀察到 Superlatch 使用大量 EX 存取模式後，SQL 引擎可以在頁面從緩衝池被捨棄後對其進行降級處理。 下圖顯示一般鎖存器和分割的超級鎖存器：

使用 SQL Server：Latches 物件和效能監視器中的相關聯計數器來收集超級閂鎖的相關資訊，包括超級閂鎖數目、每秒超級閂鎖升級次數，以及每秒超級閂鎖降級次數。 如需 SQL Server：Latches 物件及相關計數器的資訊，請參閱 SQL Server，閂鎖物件。

閂鎖等候類型

累計等候資訊是由 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_*報告。

• 非緩衝區 (非 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 請求的高等待時間。 請遵循下列步驟，診斷隨輸送量增加的平均頁面閂鎖等候時間：

  • 使用範例指令碼查詢依工作階段識別碼排序的 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_statswt_:type的平均等待時間，將總等待時間 （傳回為 wait_time_ms）除以等待任務數 （傳回為 waiting_tasks_count）。

• 在尖峰負載期間，閂鎖等待類型所花費的總等待時間百分比： 如果平均閂鎖等待時間佔整體等待時間的百分比隨著應用程式負載而增加，則閂鎖爭用可能會影響效能，因此應該進行調查。

  使用 SQL Server 等候統計資料物件 的效能計數器來測量頁鎖存器等候和非頁鎖存器等候。 然後比較這些效能計數器的值，及 CPU、I/O、記憶體和網路輸送量相關效能計數器的值。 例如，交易數/秒和批次要求數/秒，是兩個很好的資源使用率量值。

  注意

  每個等候類型的相對等候時間不會包含在 DMV 中 sys.dm_os_wait_stats ，因為此 DMW 會測量自上次啟動 SQL Server 執行個體或使用 DBCC SQLPERF重設累積等候統計資料以來的等候時間。 若要計算每個等候類型的相對等候時間，請拍攝 sys.dm_os_wait_stats 在尖峰負載前後的快照集，然後計算差異。 範例指令碼計算一段時間內的等候時間可用於此用途。

  僅限非實際執行環境，請使用下列命令清除 sys.dm_os_wait_stats DMV：

  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 DMV 中顯示的緩衝區閂鎖爭用表現為等待時間的增加，對應於 PAGELATCH_* 的閂鎖，其類型為 PAGEIOLATCH_* 或 sys.dm_os_wait_stats。 若要即時查看系統，請在系統上執行下列查詢，以聯結 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 型樹狀結構 (其定義為只有 2 到 3 個索引深度)。

即使在 B 型樹狀目錄中的存取為隨機 (例如，當非循序資料行是叢集索引的前置索引鍵)，也可能發生閂鎖競爭。 下列螢幕擷取畫面是系統中出現這類閂鎖競爭時的情況。 在此範例中，因為小型資料列與較淺層 B 型樹狀結構導致頁面密集，進而引發競爭。 當並行增加時，因為 GUID 是索引中的前置資料行，所以即使在 B 型樹狀結構中的插入為隨機，也會在頁面上引發閂鎖競爭。

在下列螢幕擷取畫面中，緩衝區資料頁面與分頁可用空間 (PFS) 頁面上皆發生等候。 即使已增加資料檔案的數目，緩衝區資料頁上同樣很容易發生閂鎖競爭。

下表會摘要說明此類閂鎖競爭主要因素的觀察結果：

索引中淺層 B 型樹狀結構與隨機插入的組合，很容易在 B 型樹狀結構中造成頁面分割。 若要執行頁面分割，SQL Server 必須取得所有層級的共用 （SH） 閂鎖，然後在 B 樹狀結構中涉及頁面分割的頁面上取得獨佔 （EX） 閂鎖。 此外，當並行性很高且資料不斷插入和刪除時，可能會發生 B 樹狀結構根分割。 在此情況下，其他插入可能必須等待在 B 樹狀結構上取得的任何非緩衝區閂鎖。 這表現為在 DMV 中ACCESS_METHODS_HOBT_VIRTUAL_ROOT觀察到的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 或 Store 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 使用循序遞增的值 TransactionID 產生，以確保 B 樹狀結構的均勻分佈：

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 取模運算或是 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 家商店在一個具有 256 GB 記憶體的 8 插槽、32 個實體核心系統上的 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 欄位填補行，以確保每一行都使用一整頁。 當整體資料規模較小，且您需要解決由下列因素組合所引起的頁面閂鎖爭用時，此策略可作為一個選項：

• 較小資料列大小
• 淺層 B 型樹狀結構
• 具有高比率隨機插入、選取、更新及刪除作業的存取模式
• 暫存佇列資料表等的小型資料表

透過填補列以佔用完整頁面，您需要 SQL 配置更多頁面，讓更多頁面可供插入，並減少 EX 頁面閂鎖爭用。

填補列以確保每一列佔據整頁

您可以使用類似於以下的指令碼來填補資料列，以佔用整個頁面：

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

這項技術是用於完整說明；而在實務上，SQLCAT 只在單一效能參與中，於具有 10,000 個資料列的小型資料表上使用此技術。 因為這項技術會增加 SQL Server 上大型資料表的記憶體壓力，且可能導致非分葉頁面上發生非緩衝區閂鎖競爭，所以其應用有限。 額外的記憶體壓力可能是應用此技術的一個重要限制因素。 伴隨新式伺服器中可用的記憶體數量，OLTP 工作負載的大部分工作集通常會保存在記憶體中。 當資料集增長到超出記憶體容納範圍時，效能會大幅下降。 因此，這項技術只適用於小型資料表。 SQLCAT 不會將此技術用於大型資料表的最後一頁/尾端頁面插入爭用等案例。

附錄：SQL Server 閂鎖競爭指令碼

本節包含可用於幫助診斷和疑難排解閂鎖爭用問題的指令碼。

查詢依工作階段識別碼排序的 sys.dm_os_waiting_tasks

下列範例指令碼會查詢 sys.dm_os_waiting_tasks，並傳回依工作階段識別碼排序的閂鎖等候：

-- 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);

相關內容

• 效能監視及微調工具