在我之前關于SQLServer 系統數據庫的文章中，我們了解了作為SQLServer 安裝一部分的每個系統數據庫。當前文章將重點介紹圍繞tempdb數據庫經常遇到的問題以及如何正確解決這些問題。

SQLServer 臨時數據庫

正如該系統數據庫的名稱所示，tempdb保存由SQLServer 創建的臨時對象。它們與多個操作相關，并充當連接到SQLServer 實例的所有用戶的全局工作區。

當用戶執行操作時，Tempdb數據庫將保存以下對象類型：

臨時對象是由用戶明確創建的。它們可以是本地或全局臨時表和索引、表變量、表值函數中使用的表和游標。

由數據庫引擎創建的內部對象，如

存儲假脫機、游標、排序和臨時大對象(LOB)的中間結果的工作表。

執行哈希聯接或哈希聚合操作時的工作文件。

如果SORT_IN_TEMPDB設置為ON，以及其他操作（如GROUPBY、ORDERBY 或SQLUNION查詢），則創建或重建索引時的中間排序結果。

支持行版本控制功能的版本存儲，通用版本存儲或在線索引構建版本存儲使用tempdb數據庫文件。

每次SQLServer 服務啟動時都會創建Tempdb數據庫。因此，可以將tempdb數據庫創建時間視為SQLServer 服務啟動時間的近似值。我們可以使用下面顯示的查詢從sys.databasesDMV 中識別它：

但是，SQLServer Service的實際啟動涉及以特定順序啟動所有系統數據庫。它可能發生得比tempdb創建時間早一點。我們可以通過在sys.dm_os_sys_infoDMV上執行以下查詢，使用sys.databasesDMV獲取值。

該ms_ticks列指定自從計算機或服務器啟動的毫秒數。sqlserver_start_time_ms_ticks列指定自SQLServer服務啟動時ms_ticks數字以來的毫秒數。

我們可以在SQLServer 錯誤日志中找到有關啟動SQLServer 服務時啟動的數據庫順序的更多信息。

在SSMS中，展開Management> SQL Server Error Logs > 打開當前錯誤日志。應用Starting updatabase過濾器并單擊Date 以升序對其進行排序：

我們可以看到，在啟動SQLServer 服務時，master數據庫已經先啟動了。然后是所有用戶數據庫和所有其他系統數據庫。最后，tempdb啟動。您還可以通過執行xp_readerrorlog系統過程以編程方式獲取此信息：

注意：如果SQLServer 服務最近沒有重新啟動，并且SQLServer錯誤日志被回收，這可能會將較舊的錯誤日志推送到較舊的文件，則上述兩種方法可能都不會顯示必要的信息。在這種情況下，我們可能需要掃描存檔的SQLServer 錯誤日志文件中的數據。

SQLTempDB 數據庫中的常見問題

由于tempdb為所有用戶會話或活動提供了一個全局工作區，如果不仔細配置，它可能成為用戶操作的性能瓶頸。在我之前的文章中，我們討論了在tempdb數據庫中實施的推薦最佳實踐。但是，即使在實施它們之后，我們也可能經常遇到問題：

跨tempdb數據文件的文件增長不均。

Tempdb數據文件正在增長到一個巨大的價值，需要縮小Tempdb。

TempDB數據文件中的文件增長不均

從SQLServer 2000 開始，默認建議是根據服務器中可用的邏輯核心數擁有多個數據文件。

當我們有多個數據文件時，例如下圖中的4個tempdb數據文件，tempdb數據文件的自動增長將以循環方式發生64MB，從tempdev> temp2 > temp3 > temp4 > tempdev >等開始在。

如果其中一個文件大小由于某種原因無法自動增長，則會導致某些文件與其他文件相比變得非常大。這會導致對大文件造成額外過載，并對tempdb數據庫產生負面性能影響。

我們需要手動確保所有tempdb數據文件在任何時間點手動均勻大小以避免爭用或性能問題，直到SQLServer 2014。從SQLServer 2016開始及之后的版本，Microsoft通過實現一些功能改變了這種行為，這些將在本文后面討論。

為了克服上述性能問題，SQLServer 引入了2個名為1117和1118的跟蹤標志以避免圍繞tempdb的爭用問題。

跟蹤標志1117– 啟用單個文件組中所有文件的自動增長

跟蹤標志1118– 為tempdb啟用UNIFORMFULL EXTENTS

跟蹤標志1117

如果沒有啟用跟蹤標志1117，每當tempdb配置有多個均勻大小的數據文件并且數據文件需要自動增長時，默認情況下SQLServer 將嘗試以循環方式增加文件大小（如果所有文件）。如果數據文件的大小不均勻，則SQLServer 將嘗試增加tempdb的最大數據文件的大小，并將使用這個較大的文件進行大多數用戶操作，從而導致tempdb爭用問題。

為了解決這個問題，SQLServer 引入了TraceFlag 1117。一旦啟用，如果文件組中的一個文件需要自動增長，它將自動增長該文件組中的所有文件。它解決了tempdb爭用問題。但是，問題是一旦啟用了跟蹤標志1117，就會為所有用戶數據庫配置自動增長。

跟蹤標志1118

跟蹤標志1118用于啟用UNIFORMFULL EXTENTS。讓我們退一步來了解SQLServer 如何存儲基本數據。

頁是SQLServer 中的基本存儲單位，大小為8千字節(KB)。

Extent是一組8個物理上連續的頁面，大小為64KB(8*8KB)。根據Extent中存儲數據的對象或所有者的數量，Extent可以分為：

UniformExtents是由單個對象或所有者使用或訪問的8個連續頁面；

MixedExtents – 8 個連續頁面被最少2個和最多8個對象或所有者使用或訪問

啟用跟蹤標志1118將允許tempdb具有統一的范圍，從而獲得更好的性能。

如何啟用跟蹤標志1117和1118

可以通過多種方法啟用跟蹤標志。您可以從以下選項中定義合適的方式：

SQLServer 服務啟動參數

即使在SQL服務重新啟動后也永久可用。推薦的方法是通過SQLServer 服務啟動參數啟用TraceFlags 1117 和1118。

打開SQLServer Configuration Manager并單擊SQLServer Services以列出該服務器中的可用服務：

右鍵單擊SQLServer (MSSQLSERVER)>Properties>StartupParameters。

在空白字段中鍵入–T以指示TraceFlag。

提供值1117和1118，如下所示。

單擊Add將跟蹤標志添加為啟動參數。

然后單擊 OK為SQLServer 的此實例永久添加跟蹤標志。重新啟動SQLServer Service以反映更改。

DBCCTRACEON (, -1)

全局啟用跟蹤標志。SQLServer 服務將在服務重新啟動時丟失跟蹤標志。要全局啟用跟蹤標志，請在新的查詢窗口中執行以下腳本：

DBCCTRACEON ()

在會話級別啟用跟蹤標志。它僅適用于用戶創建的當前會話。要在會話級別啟用跟蹤標志，請在新查詢窗口中執行以下腳本：

要查看在SQLServer 實例中啟用的跟蹤標志列表，我們可以使用DBCCTRACESTATUS命令：

正如我們所見，跟蹤標志1117和1118在我的實例中與Session一起全局啟用。

要關閉跟蹤標志，我們可以使用DBCCTRACEOFF 命令，例如：

SQLServer 2016 TempDB 增強功能

在從SQLServer 2000 到SQLServer 2014 的SQLServer 版本中，我們必須啟用跟蹤標志1117和1118以及對tempdb的完整監控以避免tempdb爭用問題。從SQLServer 2016 及更高版本開始，默認實現跟蹤標志1117和1118。

但是，根據我的個人經驗，最好將tempdb預先增長到一個巨大的大小，以避免多次自動增長的需要，并消除不均勻的文件大小或SQLServer 廣泛使用的單個文件。

我們可以驗證TraceFlag 1117 和1118在SQLServer 2016 中是如何實現的：

設置文件組內所有文件的自動增長的跟蹤標志1117現在是文件組的屬性。我們可以在創建新文件組或修改現有文件組時對其進行配置。

要驗證Filegroup的自動增長屬性，請從sys.filegroupsDMV執行以下腳本：

要修改AdventureWorks數據庫的主文件組的自動增長屬性，我們使用AUTOGROW_ALL_FILES執行以下腳本以平均自動增長所有文件或使用AUTOGROW_SINGLE_FILE僅允許自動增長單個數據文件。

默認情況下，為tempdb和從SQLServer 2016 開始的所有用戶數據庫啟用設置數據文件的統一范圍屬性的跟蹤標志1118。我們無法更改tempdb的屬性，因為它現在僅支持UniformExtent 選項。

對于用戶數據庫，我們可以修改這個參數。默認情況下，系統數據庫master、model和msdb支持混合范圍，并且也不能更改。

要修改用戶數據庫的混合頁面分配屬性值，請使用以下腳本：

為了驗證混合頁分配屬性，我們可以從sys.databasesDMV查詢is_mixed_page_allocation_on列，值為0，表示統一范圍頁分配，1表示混合范圍頁分配。

TempDB數據文件變大，需要壓縮TempDB

在SQLServer2014或更早的版本中，如果沒有將跟蹤標志1117和1118與tempdb數據庫創建的多個數據文件正確配置，其中一些文件將不可避免地變大。如果發生這種情況，DBA通常會嘗試縮小tempdb數據文件。但這是一個處理這種情況不恰當的方法。

還有其他選項可用于縮小tempdb。

讓我們考慮可用于Shrinktempdb 的DBCC命令以及執行這些操作的影響。

DBCC收縮數據庫

該DBCCSHRINKDATABASE控制臺命令是通過縮小數據日志文件的末尾。

要成功收縮數據庫，該命令需要文件末尾的可用空間。如果文件末尾有任何活動事務，則無法縮小數據庫文件。

執行DBCCSHRINKDATABASE的影響是它會嘗試清除每個數據文件或日志文件末尾的可用空間，這些空間可能已為表數據的未來增長保留。因此，運行此命令可能會導致文件大小不均勻，從而導致tempdb爭用問題。

縮小用戶數據庫（例如Adventureworks數據庫）的語法是

DBCC收縮文件

該DBCCSHRINKFILE控制臺命令的工作原理類似DBCCSHRINKDATABASE，但它縮小了指定的數據庫數據或日志文件。

如果您發現某個特定的tempdb數據文件很大，我們可以嘗試使用DBCCSHRINKFILE 縮小該特定項目，如下所示。

在tempdb上使用此命令時要小心，因為如果文件收縮到低于或高于其他數據文件的值，則該特定數據文件將無法有效使用。或者，它會被更頻繁地使用，從而導致tempdb爭用問題。

在AdventureWorks數據文件上執行DBCCSHRINKFILE 操作到1GB(1024 MB) 的語法為：

DBCCDROPCLEANBUFFERS

DBCCDROPCLEANBUFFERS控制臺命令用于清除Buffer池中的所有干凈緩沖區，以及columnstore對象池中的columnstore對象。

只需執行以下命令：

DBCCFREEPROCCACHE

該DBCCFREEPROCCACHE命令清除所有的存儲過程的執行計劃緩存。

SQLServer 使用過程執行計劃緩存來更快地執行相同的過程調用。執行DBCCFREEPROCCACHE 后，PlanCache 被清除。因此，當在實例中執行存儲過程時，SQLServer 必須再次創建該緩存。在生產數據庫實例中執行時會留下嚴重的負面影響。

不建議在生產數據庫實例上執行DBCCFREEPROCCACHE！

執行DBCCFREEPROCCACHE 的語法如下：

DBCCFREESESSIONCACHE

該DBCCFREESESSIONCACHE命令將清除SQLServer實例的分布查詢連接緩存。當在特定SQLServer 實例上執行許多分布式查詢時，這將很有幫助。

執行DBCCFREESESSIONCACHE 的語法是：

DBCCFREESYSTEMCACHE

該DBCCFREESYSTEMCACHE命令清除所有緩存中的所有未使用的緩存條目。默認情況下，SQLServer 這樣做是為了讓更多內存可用于新操作。但是，我們可以使用以下命令手動執行它：

.眾所周知，tempdb存儲所有臨時用戶對象或內部對象，包括執行計劃緩存、緩沖池數據、會話緩存和系統緩存。因此，執行上述6條DBCC命令將有助于清除阻止正常收縮過程的tempdb數據文件。

盡管我們已經通過各種方法完成了如何縮小tempdb的步驟，但下面列出了處理tempdb數據庫的推薦最佳實踐：

A.如果可能，重新啟動SQLServer 服務以均勻地重新創建tempdb數據文件。潛在的影響是，我們將丟失上面討論的所有執行計劃和其他緩存信息。

B.將tempdb數據文件預增長到保存tempdb數據文件的驅動器中可用的巨大文件大小。這將防止SQLServer 在SQLServer 2014 及更早版本中不均勻地增加文件大小。

如果由于RTO或RPO導致SQLServer 服務無法重新啟動，請在清楚了解影響后嘗試上述DBCC命令。

D.收縮tempdb數據庫或數據文件不是推薦的方法，因此永遠不要在您的生產環境中這樣做，除非沒有其他選擇。

結論

我們已經了解了有關tempdb工作原理的更多信息，以便我們可以配置tempdb以獲得更好的性能，從而避免tempdb上的爭用問題。我們還討論了tempdb中經常遇到的問題、SQLServer 中跨各種版本的可用措施以及如何有效地處理它。除此之外，我們還研究了為什么在處理tempdb數據庫時不推薦使用收縮tempdb數據庫或數據文件的方法。

審核編輯 ：李倩