減少 SQL Server tempdb 資料庫中的分攤爭用的建議

本文可協助您解決問題,當伺服器出現負載過重時,您注意到嚴重的封鎖。

原始產品版本:   Sqlserver
原始 KB 編號:   2154845

徵狀

在執行 Microsoft SQL Server 的伺服器上,當伺服器出現繁重的負載時,您會注意到嚴重的封鎖。 動態管理檢視 [ sys.dm_exec_requestsys.dm_os_waiting_tasks ] 表示這些要求或工作正在等候 tempdb 資源。 此外,等待類型為 PAGELATCH_UP ,而等待資源指向 tempdb 中的頁面。 在 tempdb) 中,這些頁面的格式可能是2:1:1、2:1:3 等等 (PFS 和 SGAM 頁面。

注意

如果頁面平均可被8088整除,則為 PFS 頁面。 例如,頁面2:3:905856 是 tempdb 中 file_id = 3 的 PFS。

下列作業會大量使用 tempdb

  • (local 或 global) 的臨時資料表的重複建立及放置作業。
  • 使用 tempdb 進行儲存的表變數。
  • 與資料指標關聯的工作表。
  • 與 ORDER BY 子句相關聯的工作表。
  • 與 GROUP BY 子句相關聯的工作表。
  • 與雜湊計畫相關聯的工作檔案。

這些活動可能會造成爭用問題。

原因

tempdb 資料庫大量使用時,SQL Server 在嘗試分配頁面時可能會遇到爭用現象。 根據爭奪的程度而定,這可能會導致涉及 tempdb 的查詢和要求暫時沒有回應。

建立物件時,必須從混合的盤區中為兩個 (2) 頁面分配兩個,並將其指派給新的物件。 一個頁面用於索引配置對應 (IAM) ,第二個頁面是用於物件的第一頁。 SQL Server 會使用共用全域分配對應 (SGAM) 頁面追蹤混合盤區。 每個 SGAM 頁面會追蹤大約 4 gb 的資料。

若要從混合的盤區中分配頁面,SQL Server 必須掃描 [頁面空閒空間] (PFS) ] 頁面,以判斷哪些混合式頁面可供自由分配。 PFS 頁面會在每個頁面上追蹤可用空間,每個 PFS 頁面會追蹤大約8000頁面。 維護適當的同步處理,以變更 PFS 和 SGAM 頁面;而且這可讓短句點延遲其他修飾符。

當 SQL Server 搜尋混合頁面以進行指派時,它一定會在相同的檔案和 SGAM 頁面上啟動掃描。 這會在多個混合頁面進行的情況時,在 SGAM 頁面上造成強烈的爭用。 這可能會導致 [ 徵兆 ] 區段中所述的問題。

注意

取消分派活動也必須修改頁面。 這可能會導致爭奪爭用增加。

若要深入瞭解 SQL Server (SGAM,GAM,PFS,IAM) 所使用的不同分配機制,請參閱 參考 一節。

解決方案

增加大小相等的 tempdb 資料檔案數目

舉例來說,如果 tempdb 的單一資料檔案大小是 8 gb,而且記錄檔大小是 2 gb,建議您將資料檔案的數目增加為 8 (8) (每個 1 GB 保留相同的大小) ,並保留記錄檔。 將不同的資料檔案存放在不同的磁片上,將會提供額外的效能效益。 不過,這不是必要的。 檔案可能會在相同的磁片區上共存。

Tempdb 資料檔案的最佳數目取決於 tempdb 中所看到的爭用程度。 作為起點,您可以設定 tempdb 至少等於為 SQL Server 指派的邏輯處理器數目。 對於高端系統,起始號碼可以是八 (8) 。 如果爭用未降低,您可能需要增加資料檔案的數目。

建議您使用相同大小的資料檔案。 SQL Server 2000 Service Pack 4 (SP4) 引進了修正,使用了混合式頁面分配的迴圈法演算法。 由於這項改進,當) 有一個以上的檔案存在時,每個連續的混合頁面分配 (,起始檔案會有所不同。 為 SGAM 新增的配置演算法是純迴圈的,不服從比例填滿以維持速度。 建議您以相同大小建立所有 tempdb 資料檔案。

增加 tempdb 資料檔案數目可減少爭用的程度

下列清單說明如何增加大小相等的 tempdb 資料檔案數量,可減少爭用情況:

  • 如果您有一個 tempdb 的資料檔案,則只有一個 GAM 頁面,每個 4 GB 的空間都有一個 SGAM 頁面。

  • 增加具有相同大小的 tempdb 的資料檔案數量,可有效地為每個資料檔案建立一個或多個 GAM 和 SGAM 頁面。

  • GAM 的設定檔演算法一次會在一段時間內分配一個範圍 (八個連續頁面) 從檔的檔案數目,但同時又會考慮以比例填滿的方式。 因此,如果您有10個大小相同的檔案,第一個分配是從 File1,第二個從 File2,第三個是 File3,依此類推。

  • PFS 頁面上的資源爭用會減少,因為每次8個頁面會標示為已滿,因為 GAM 正在分配頁面。

如何執行追蹤旗標-T1118 減少爭用

注意

本節僅適用于 SQL Server 2014 和更舊的版本。

下列清單說明如何使用追蹤旗標 ,T1118 減少爭用的情況:

  • -T1118 是一種整個伺服器的設定。
  • 在 SQL Server 的 Startup 參數中包含 -T1118 追蹤標記,這樣即使在回收 sql server 之後,追蹤旗標仍然有效。
  • -T1118 移除伺服器上幾乎所有的單一頁面分配。
  • 停用大多數的單一頁面分配,可減少 SGAM 頁面上的爭用情況。
  • 如果已開啟 -T1118 ,幾乎所有從 GAM 頁面進行的新的分攤 (例如,2:1:2) ,) (可在物件中一次, (一個範圍) 一個範圍 (一個範圍,而不是從物件的前八個) 8 的頁面(沒有追蹤旗標)的單一頁面。
  • 即使已開啟 -T1118 ,IAM 頁面仍會使用來自 SGAM 頁面的單一頁面 分配。 不過,當它結合修復8.00.0702 及增加的 tempdb 資料檔時,net 效果可減少 SGAM 頁面上的爭用。 如需空間考慮,請參閱下一節。

缺點

使用 -T1118 的缺點是,當下列條件成立時,您可能會看到資料庫大小增加:

  • 在使用者資料庫中建立新的物件。
  • 每個新物件佔用的儲存體低於 64 KB。

如果這些條件成立,您可以將 64 KB (八個頁面 * 8 KB = 64 KB) ,只需要 8 KB 的空間,因此會浪費 storage 的 56 KB。 不過,如果新物件使用超過 64 KB (八個) 頁面在其存留期內,則追蹤旗沒有任何缺點。 因此,在最壞的情況下,SQL Server 可能會在第一次分配期間,只針對永不超過一個 (1) 頁面的新物件,分配 7 (7) 其他頁面。

參考