減少 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) 所使用的不同配置機制，請參閱參考一節。
解決方案
SQL Server 2016 和更新版本：
檢閱
優化 SQL Server 中的tempdb資料庫效能。
套用 SQL Server 2016 和 2017 的相關 CU，以利用下列更新。 已進行改善，進一步減少 2016 SQL Server 和 2017 SQL Server 爭用。 除了跨所有tempdb資料檔的迴圈配置資源配置之外，此修正還可在相同數據檔的數個 PFS 頁面上執行迴圈配置資源，以改善 PFS 頁面配置。 如需詳細資訊，請參閱 KB4099472 - SQL Server 2014、2016 和 2017 中的 PFS 頁面迴圈配置資源演算法改進。
如需這些建議的詳細資訊，以及 SQL 2016 檢閱中導入的其他變更
SQL Server 2014 和更早版本：
若要改善tempdb的並行，請嘗試下列方法：
增加 tempdb 中的數據檔數目，以將磁碟頻寬最大化，並減少配置結構中的爭用。 根據規則，如果邏輯處理器的數目小於或等於八個 (8) ，請使用與邏輯處理器相同的數據檔數目。 如果邏輯處理器的數目大於 8 (8) ，請使用八個數據檔。 如果競爭持續發生，請將數據檔數目增加四個 (4 的倍數，最多) 邏輯處理器數目，直到競爭降低為可接受的層級為止。 或者，對工作負載或程式代碼進行變更。
請考慮在 2005 SQL Server 使用 tempdb 中實作最佳做法建議。
如果先前的步驟並未大幅減少配置爭用，而且爭用在 SGAM 頁面上，請實作追蹤旗標 -T1118。 在此追蹤旗標下，SQL Server 會將完整範圍配置給每個資料庫物件，藉此消除 SGAM 頁面上的爭用。
注意事項
此追蹤旗標會影響 SQL Server 實例上的每個資料庫。 如需如何判斷配置爭用是否在 SGAM 頁面上的資訊，請參閱 DML 作業所造成的監視爭用。
針對 SQL Server 2014 環境，請確定您套用 Service Pack 3 以利用下列 KB 文章中所述的修正程式。 改善可進一步減少 SQL Server 2014 環境中的競爭。 除了跨所有tempdb資料檔的迴圈配置資源配置之外，此修正還可在相同數據檔的數個 PFS 頁面上執行迴圈配置資源，以改善 PFS 頁面配置。
KB4099472 - SQL Server 2014、2016 和 2017 中的 PFS 頁面迴圈配置資源演演算法改進
MSSQL Tiger 小組部落格：SQL Server tempdb 中的檔案和追蹤旗標和更新
增加大小相等的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) 頁面的新物件。