在我之前關(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)部對象,如
存儲假脫機(jī)、游標(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列指定自從計算機(jī)或服務(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(jìn)行排序:
我們可以看到,在啟動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ū),如果不仔細(xì)配置,它可能成為用戶操作的性能瓶頸。在我之前的文章中,我們討論了在tempdb數(shù)據(jù)庫中實施的推薦最佳實踐。但是,即使在實施它們之后,我們也可能經(jīng)常遇到問題:
跨tempdb數(shù)據(jù)文件的文件增長不均。
Tempdb數(shù)據(jù)文件正在增長到一個巨大的價值,需要縮小Tempdb。
TempDB數(shù)據(jù)文件中的文件增長不均
從SQLServer 2000 開始,默認(rèn)建議是根據(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)生負(fù)面性能影響。
我們需要手動確保所有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ù)文件需要自動增長時,默認(rèn)情況下SQLServer 將嘗試以循環(huán)方式增加文件大小(如果所有文件)。如果數(shù)據(jù)文件的大小不均勻,則SQLServer 將嘗試增加tempdb的最大數(shù)據(jù)文件的大小,并將使用這個較大的文件進(jìn)行大多數(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 (
全局啟用跟蹤標(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 增強(qiáng)功能
在從SQLServer 2000 到SQLServer 2014 的SQLServer 版本中,我們必須啟用跟蹤標(biāo)志1117和1118以及對tempdb的完整監(jiān)控以避免tempdb爭用問題。從SQLServer 2016 及更高版本開始,默認(rèn)實現(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)有文件組時對其進(jìn)行配置。
要驗證Filegroup的自動增長屬性,請從sys.filegroupsDMV執(zhí)行以下腳本:
要修改AdventureWorks數(shù)據(jù)庫的主文件組的自動增長屬性,我們使用AUTOGROW_ALL_FILES執(zhí)行以下腳本以平均自動增長所有文件或使用AUTOGROW_SINGLE_FILE僅允許自動增長單個數(shù)據(jù)文件。
默認(rèn)情況下,為tempdb和從SQLServer 2016 開始的所有用戶數(shù)據(jù)庫啟用設(shè)置數(shù)據(jù)文件的統(tǒng)一范圍屬性的跟蹤標(biāo)志1118。我們無法更改tempdb的屬性,因為它現(xiàn)在僅支持UniformExtent 選項。
對于用戶數(shù)據(jù)庫,我們可以修改這個參數(shù)。默認(rèn)情況下,系統(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)重的負(fù)面影響。
不建議在生產(chǎn)數(shù)據(jù)庫實例上執(zhí)行DBCCFREEPROCCACHE!
執(zhí)行DBCCFREEPROCCACHE 的語法如下:
DBCCFREESESSIONCACHE
該DBCCFREESESSIONCACHE命令將清除SQLServer實例的分布查詢連接緩存。當(dāng)在特定SQLServer 實例上執(zhí)行許多分布式查詢時,這將很有幫助。
執(zhí)行DBCCFREESESSIONCACHE 的語法是:
DBCCFREESYSTEMCACHE
該DBCCFREESYSTEMCACHE命令清除所有緩存中的所有未使用的緩存條目。默認(rèn)情況下,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ù)文件不是推薦的方法,因此永遠(yuǎn)不要在您的生產(chǎn)環(huán)境中這樣做,除非沒有其他選擇。
結(jié)論
我們已經(jīng)了解了有關(guān)tempdb工作原理的更多信息,以便我們可以配置tempdb以獲得更好的性能,從而避免tempdb上的爭用問題。我們還討論了tempdb中經(jīng)常遇到的問題、SQLServer 中跨各種版本的可用措施以及如何有效地處理它。除此之外,我們還研究了為什么在處理tempdb數(shù)據(jù)庫時不推薦使用收縮tempdb數(shù)據(jù)庫或數(shù)據(jù)文件的方法。
審核編輯 :李倩
-
SQL
+關(guān)注
關(guān)注
1文章
762瀏覽量
44117 -
數(shù)據(jù)庫
+關(guān)注
關(guān)注
7文章
3794瀏覽量
64362
原文標(biāo)題:SQL Server 系統(tǒng)數(shù)據(jù)庫- Tempdb 維護(hù)
文章出處:【微信號:哲想軟件,微信公眾號:哲想軟件】歡迎添加關(guān)注!文章轉(zhuǎn)載請注明出處。
發(fā)布評論請先 登錄
相關(guān)推薦
評論