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

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

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

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

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

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

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

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

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

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

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

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

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

該ms_ticks列指定自從計(jì)算機(jī)或服務(wù)器啟動(dòng)的毫秒數(shù)。sqlserver_start_time_ms_ticks列指定自SQLServer服務(wù)啟動(dòng)時(shí)ms_ticks數(shù)字以來(lái)的毫秒數(shù)。

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

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

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

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

SQLTempDB 數(shù)據(jù)庫(kù)中的常見問(wèn)題

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

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

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

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

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

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

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

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

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

跟蹤標(biāo)志1117– 啟用單個(gè)文件組中所有文件的自動(dòng)增長(zhǎng)

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

跟蹤標(biāo)志1117

如果沒(méi)有啟用跟蹤標(biāo)志1117，每當(dāng)tempdb配置有多個(gè)均勻大小的數(shù)據(jù)文件并且數(shù)據(jù)文件需要自動(dòng)增長(zhǎng)時(shí)，默認(rèn)情況下SQLServer 將嘗試以循環(huán)方式增加文件大?。ㄈ绻形募?。如果數(shù)據(jù)文件的大小不均勻，則SQLServer 將嘗試增加tempdb的最大數(shù)據(jù)文件的大小，并將使用這個(gè)較大的文件進(jìn)行大多數(shù)用戶操作，從而導(dǎo)致tempdb爭(zhēng)用問(wèn)題。

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

跟蹤標(biāo)志1118

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

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

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

UniformExtents是由單個(gè)對(duì)象或所有者使用或訪問(wèn)的8個(gè)連續(xù)頁(yè)面；

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

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

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

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

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

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

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

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

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

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

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

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

DBCCTRACEON (, -1)

全局啟用跟蹤標(biāo)志。SQLServer 服務(wù)將在服務(wù)重新啟動(dòng)時(shí)丟失跟蹤標(biāo)志。要全局啟用跟蹤標(biāo)志，請(qǐng)?jiān)谛碌牟樵兇翱谥袌?zhí)行以下腳本：

DBCCTRACEON ()

在會(huì)話級(jí)別啟用跟蹤標(biāo)志。它僅適用于用戶創(chuàng)建的當(dāng)前會(huì)話。要在會(huì)話級(jí)別啟用跟蹤標(biāo)志，請(qǐng)?jiān)谛虏樵兇翱谥袌?zhí)行以下腳本：

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

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

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

SQLServer 2016 TempDB 增強(qiáng)功能

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

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

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

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

要驗(yàn)證Filegroup的自動(dòng)增長(zhǎng)屬性，請(qǐng)從sys.filegroupsDMV執(zhí)行以下腳本：

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

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

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

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

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

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

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

還有其他選項(xiàng)可用于縮小tempdb。

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

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

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

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

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

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

DBCC收縮文件

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

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

在tempdb上使用此命令時(shí)要小心，因?yàn)槿绻募湛s到低于或高于其他數(shù)據(jù)文件的值，則該特定數(shù)據(jù)文件將無(wú)法有效使用?；蛘?，它會(huì)被更頻繁地使用，從而導(dǎo)致tempdb爭(zhēng)用問(wèn)題。

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

DBCCDROPCLEANBUFFERS

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

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

DBCCFREEPROCCACHE

該DBCCFREEPROCCACHE命令清除所有的存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃緩存。

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

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

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

DBCCFREESESSIONCACHE

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

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

DBCCFREESYSTEMCACHE

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

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

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

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

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

如果由于RTO或RPO導(dǎo)致SQLServer 服務(wù)無(wú)法重新啟動(dòng)，請(qǐng)?jiān)谇宄私庥绊懞髧L試上述DBCC命令。

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

結(jié)論

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

審核編輯 ：李倩