診斷與解決 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_*報告。

• 非緩衝區（Non-BUF）閂鎖： 用於確保緩衝集區頁面以外的任何記憶體內部結構的一致性。 任何等待非緩衝區鎖存的情況都會被報告為 wait_type 或 LATCH_*。

• I/O 閂鎖： 緩衝區閂鎖的子集；當由緩衝區閂鎖保護的相同結構需要透過 I/O 作業載入至緩衝集區時，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 重設後的累計值。 使用 sys.dm_os_sys_info 中的 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 等待。 這會透過 sys.dm_os_waiting_tasks DMV 中的 wait_type 值顯示。

這項競爭因為發生在 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 的等待，這是一般常見的情況。 若要檢視 Page Latch waits 與 Tree Page Latch waits 之間的差異，請使用 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 會是自然值，可以用來在鍵值範圍內分配插入。 此技術需要建立複合索引鍵，其中前導索引鍵欄是所識別資料行的值，或該值的某些雜湊與一或多個額外資料行結合，以提供唯一性。 在大部分情況下，值的雜湊效果最佳，因為太多不同的值會導致實體組織不佳。 例如，在銷售點（POS）系統中，可以根據 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 核心數目小一些值。 擁有更多分割區可能會導致必須搜尋所有分割區的查詢增加更多額外負荷，在這些情況下，較少的分割區會有所幫助。 在 SQLCAT 針對 64 與 128 個邏輯 CPU 系統、使用實際客戶工作負載進行的測試中，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);

相關內容

• 效能監視及微調工具