減少 SQL Server tempdb 資料庫中配置爭用的建議

  • 發行項

本文可協助您解決當伺服器遇到大量負載時，您注意到嚴重封鎖的問題。

原始產品版本： SQL S
原始 KB 編號： 2154845

徵狀

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

注意事項

如果頁面可平均除以 8088，則為 PFS 頁面。 例如，第 2：3：905856 頁是 tempdb 中 file_id=3 中的 PFS。

下列作業廣泛使用 tempdb ：

  • 在本機或全域) (臨時表的重複建立和卸載作業。
  • 使用 tempdb 進行記憶體的數據表變數。
  • 與 CURSORS 相關聯的工作數據表。
  • 與 ORDER BY 子句相關聯的工作數據表。
  • 與 GROUP BY 子句相關聯的工作數據表。
  • 與HASH PLANS相關聯的工作檔案。

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

原因

大量使用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 (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 頁面的資源爭用減少，因為 GAM 正在配置頁面，所以一次有八個頁面標示為 FULL。

如何實作追蹤旗標 -T1118 減少爭用

注意事項

本節僅適用於 SQL Server 2014 和更早版本。

下列清單說明如何使用追蹤旗標 -T1118 來減少爭用：

  • -T1118 是全伺服器設定。
  • 在 SQL Server 的 Startup 參數中包含 -T1118 追蹤旗標，如此一來，即使在回收 SQL Server 之後，追蹤旗標仍會保持作用中。
  • -T1118 會移除伺服器上幾乎所有的單一頁面配置。
  • 藉由停用大部分的單一頁面配置，您可以減少 SGAM 頁面上的爭用。
  • 如果 -T1118 已開啟，則幾乎所有的新配置都是從 GAM 頁面 (，例如，2：1：2) 會配置 8 個 (8 個) 頁，一次 (一個範圍) 物件，而不是物件前八個 (8 個) 頁面的單一頁面， 不含追蹤旗標。
  • 即使 -T1118已開啟，IAM 頁面仍會使用 SGAM 頁面中的單一頁面配置。 不過，當它與 Hotfix 8.00.0702 結合並增加 tempdb 數據檔時，淨效果就是減少 SGAM 頁面上的爭用。 如需空間考慮，請參閱下一節。

缺點

使用 -T1118 的缺點是，如果下列條件成立，您可能會看到資料庫大小增加：

  • 新的物件會建立在用戶資料庫中。
  • 每個新物件佔用的記憶體少於 64 KB。

如果這些條件成立，您可以為只需要 8 KB 空間的物件配置 64 KB (8 個頁面 * 8 KB = 64 KB) ，因而浪費 56 KB 的記憶體。 不過，如果新物件在其存留期內使用超過 64 KB (8 頁) ，追蹤旗標就不會有缺點。 因此，在最糟的情況下，SQL Server 可能會在第一次配置期間配置七 (7) 額外頁面，只針對從未成長超過一個 (1) 頁面的新物件。

參考資料