共用方式為

請參閱在 SQL Server tempdb 資料庫中降低配置競爭的建議。

  • 發行項

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

原始產品版本：SQL Server
原始 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），請參閱參考一節。

解決方法

  • SQL Server 2016 和更新版本：

    檢閱

    如需這些建議和其他 SQL 2016 檢閱中導入之變更的詳細資訊

  • SQL Server 2014 和舊版：

    若要改善tempdb的並行存取，請嘗試下列方法：

    • 增加tempdb中的數據檔數目，以將磁碟頻寬最大化，並減少配置結構中的爭用。 根據規則，如果邏輯處理器數目小於或等於八（8），請使用與邏輯處理器相同的數據檔數目。 如果邏輯處理器的數目大於八（8），請使用八個數據檔。 如果爭用持續發生，請將四個 （4） 的數據檔數目增加到邏輯處理器的數目，直到爭用縮減為可接受的層級為止。 或者，變更工作負載或程序代碼。

    • 請考慮在 SQL Server 2005 中使用 tempdb 中實作最佳做法建議。

    • 如果先前的步驟不會大幅減少配置爭用，而且爭用位於SGAM頁面上，請實作追蹤旗標 -T1118。 在此追蹤旗標下，SQL Server 會將完整範圍配置給每個資料庫物件，藉此消除 SGAM 頁面上的爭用。

      注意

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

例如，如果 tempdb 的單一數據檔大小為 8 GB，而記錄檔大小為 2 GB，建議將數據檔數目增加至 8（每個 1 GB 以維持相等大小），並將記錄檔保留為正則。 將不同的數據檔放在不同的磁碟上，將會提供額外的效能優點。 不過，這並非必要。 檔案可以共存於相同的磁碟區上。

tempdb 數據檔的最佳數目取決於 tempdb 中所見爭用的程度。 作為起點，您可以將tempdb設定為至少等於為SQL Server指派的邏輯處理器數目。 對於高端系統，起始數位可能是8（8）。 如果未減少爭用，您可能必須增加資料檔的數目。

建議您使用相等大小數據檔。 SQL Server 2000 Service Pack 4 （SP4） 引進了使用迴圈配置資源演算法進行混合分頁配置的修正程式。 由於這項改進，每個連續混合頁面配置的起始檔案都不同（如果有一個以上的檔案存在）。 SGAM 的新配置演算法是純迴圈配置資源，且不接受比例填滿以維持速度。 建議您使用相同的大小建立所有 tempdb 資料檔。

增加 tempdb 資料檔數目如何減少爭用

下列清單說明如何增加等於大小調整的 tempdb 資料檔數目會減少爭用：

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

  • 增加 tempdb 具有相同大小的資料檔數目，實際上會為每個數據檔建立一或多個 GAM 和 SGAM 頁面。

  • GAM 的配置演算法會一次配置一個範圍（八個連續頁面），以迴圈配置資源方式的檔案數目，同時接受比例填滿。 因此，如果您有 10 個相同大小的檔案，第一個配置來自 File1、File2 中的第二個、File3 中的第三個等等。

  • PFS 頁面的資源爭用會減少，因為一次有 8 個頁面標示為 FULL，因為 GAM 正在配置頁面。

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

注意

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

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

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

缺點

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

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

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

參考資料