SQL Server系統(tǒng)數(shù)據(jù)庫-Tempdb維護

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

SQLServer 臨時數(shù)據(jù)庫

正如該系統(tǒng)數(shù)據(jù)庫的名稱所示，tempdb保存由SQLServer 創(chuàng)建的臨時對象。它們與多個操作相關(guān)，并充當(dāng)連接到SQLServer 實例的所有用戶的全局工作區(qū)。

當(dāng)用戶執(zhí)行操作時，Tempdb數(shù)據(jù)庫將保存以下對象類型：

臨時對象是由用戶明確創(chuàng)建的。它們可以是本地或全局臨時表和索引、表變量、表值函數(shù)中使用的表和游標(biāo)。

由數(shù)據(jù)庫引擎創(chuàng)建的內(nèi)部對象，如

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

執(zhí)行哈希聯(lián)接或哈希聚合操作時的工作文件。

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

支持行版本控制功能的版本存儲，通用版本存儲或在線索引構(gòu)建版本存儲使用tempdb數(shù)據(jù)庫文件。

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

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

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

我們可以在SQLServer 錯誤日志中找到有關(guān)啟動SQLServer 服務(wù)時啟動的數(shù)據(jù)庫順序的更多信息。

在SSMS中，展開Management> SQL Server Error Logs > 打開當(dāng)前錯誤日志。應(yīng)用Starting updatabase過濾器并單擊Date 以升序?qū)ζ溥M行排序：

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

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

SQLTempDB 數(shù)據(jù)庫中的常見問題

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

跨tempdb數(shù)據(jù)文件的文件增長不均。

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

TempDB數(shù)據(jù)文件中的文件增長不均

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

當(dāng)我們有多個數(shù)據(jù)文件時，例如下圖中的4個tempdb數(shù)據(jù)文件，tempdb數(shù)據(jù)文件的自動增長將以循環(huán)方式發(fā)生64MB，從tempdev> temp2 > temp3 > temp4 > tempdev >等開始在。

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

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

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

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

跟蹤標(biāo)志1118– 為tempdb啟用UNIFORMFULL EXTENTS

跟蹤標(biāo)志1117

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

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

跟蹤標(biāo)志1118

跟蹤標(biāo)志1118用于啟用UNIFORMFULL EXTENTS。讓我們退一步來了解SQLServer 如何存儲基本數(shù)據(jù)。

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

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

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

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

啟用跟蹤標(biāo)志1118將允許tempdb具有統(tǒng)一的范圍，從而獲得更好的性能。

如何啟用跟蹤標(biāo)志1117和1118

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

SQLServer 服務(wù)啟動參數(shù)

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

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

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

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

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

單擊Add將跟蹤標(biāo)志添加為啟動參數(shù)。

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

DBCCTRACEON (, -1)

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

DBCCTRACEON ()

在會話級別啟用跟蹤標(biāo)志。它僅適用于用戶創(chuàng)建的當(dāng)前會話。要在會話級別啟用跟蹤標(biāo)志，請在新查詢窗口中執(zhí)行以下腳本：

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

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

要關(guān)閉跟蹤標(biāo)志，我們可以使用DBCCTRACEOFF 命令，例如：

SQLServer 2016 TempDB 增強功能

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

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

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

設(shè)置文件組內(nèi)所有文件的自動增長的跟蹤標(biāo)志1117現(xiàn)在是文件組的屬性。我們可以在創(chuàng)建新文件組或修改現(xiàn)有文件組時對其進行配置。

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

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

默認情況下，為tempdb和從SQLServer 2016 開始的所有用戶數(shù)據(jù)庫啟用設(shè)置數(shù)據(jù)文件的統(tǒng)一范圍屬性的跟蹤標(biāo)志1118。我們無法更改tempdb的屬性，因為它現(xiàn)在僅支持UniformExtent 選項。

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

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

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

TempDB數(shù)據(jù)文件變大，需要壓縮TempDB

在SQLServer2014或更早的版本中，如果沒有將跟蹤標(biāo)志1117和1118與tempdb數(shù)據(jù)庫創(chuàng)建的多個數(shù)據(jù)文件正確配置，其中一些文件將不可避免地變大。如果發(fā)生這種情況，DBA通常會嘗試縮小tempdb數(shù)據(jù)文件。但這是一個處理這種情況不恰當(dāng)?shù)姆椒ā?/p>

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

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

DBCC收縮數(shù)據(jù)庫

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

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

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

縮小用戶數(shù)據(jù)庫（例如Adventureworks數(shù)據(jù)庫）的語法是

DBCC收縮文件

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

如果您發(fā)現(xiàn)某個特定的tempdb數(shù)據(jù)文件很大，我們可以嘗試使用DBCCSHRINKFILE 縮小該特定項目，如下所示。

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

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

DBCCDROPCLEANBUFFERS

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

只需執(zhí)行以下命令：

DBCCFREEPROCCACHE

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

SQLServer 使用過程執(zhí)行計劃緩存來更快地執(zhí)行相同的過程調(diào)用。執(zhí)行DBCCFREEPROCCACHE 后，PlanCache 被清除。因此，當(dāng)在實例中執(zhí)行存儲過程時，SQLServer 必須再次創(chuàng)建該緩存。在生產(chǎn)數(shù)據(jù)庫實例中執(zhí)行時會留下嚴(yán)重的負面影響。

不建議在生產(chǎn)數(shù)據(jù)庫實例上執(zhí)行DBCCFREEPROCCACHE！

執(zhí)行DBCCFREEPROCCACHE 的語法如下：

DBCCFREESESSIONCACHE

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

執(zhí)行DBCCFREESESSIONCACHE 的語法是：

DBCCFREESYSTEMCACHE

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

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

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

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

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

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

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

結(jié)論

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

審核編輯 ：李倩