共計 2808 個字符，預計需要花費 8 分鐘才能閱讀完成。
Tempdb 對 SQL Server 性能優化有何影響，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。
先給大家鞏固 tempdb 的基礎知識
簡介：
tempdb 是 SQLServer 的系統數據庫一直都是 SQLServer 的重要組成部分，用來存儲臨時對象。可以簡單理解 tempdb 是 SQLServer 的速寫板。應用程序與數據庫都可以使用 tempdb 作為臨時的數據存儲區。一個實例的所有用戶都共享一個 Tempdb。很明顯，這樣的設計不是很好。當多個應用程序的數據庫部署在同一臺服務器上的時候，應用程序共享 tempdb，如果開發人員不注意對 Tempdb 的使用就會造成這些數據庫相互影響從而影響應用程序。
特性：
1、tempdb 中的任何數據在系統重新啟動之后都不會持久存在。因為實際上每次 SQLServer 啟動的時候都會重新創建 tempdb。這個特性就說明 tempdb 不需要恢復。
2、tempdb 始終設置為“simple”的恢復模式，當你嘗試修改時都會報錯。也就是說已提交事務的事務日志記錄在每個檢查點后都標記為重用。
3、tempdb 也只能有一個 filegroup，不能增加更多文件組。
4、tempdb 被用來存儲三種類型的對象：用戶對象，內部對象、版本存儲區
接下來圍繞主題展示問題分析：
1.SQL Server 系統數據庫介紹
SQL Server 有四個重要的系統級數據庫:master,model,msdb,tempdb.
master: 記錄 SQL Server 系統的所有系統級信息, 包括實例范圍的元數據, 端點, 鏈接服務器和系統配置設置, 還記錄其他數據庫是否存在以及這些數據問文件的位置等等. 如果 master 不可用, 數據庫將不能啟動.
model: 用在 SQL Server 實例上創建的所有數據庫的模板。因為每次啟動 SQL Server 時都會創建 tempdb，所以 model 數據庫必須始終存在于 SQL Server 系統中。
msdb: 由 SQL Server 代理用來計劃警報和作業。
tempdb: 是連接到 SQL Server 實例的所有用戶都可用的全局資源，它保存所有臨時表, 臨時工作表, 臨時存儲過程, 臨時存儲大的類型, 中間結果集, 表變量和游標等。另外，它還用來滿足所有其他臨時存儲要求.
2.tempdb 內在運行原理
與其他 SQL Server 數據庫不同的是,tempdb 在 SQL Server 停掉, 重啟時會自動的 drop,re-create. 根據 model 數據庫會默認建立一個新的 8MB(mdf file:8MB;ldf file:1MB, autogtouth 設置為 10%) 大小 recovery model 為 simple 的 tempdb 數據庫.
tempdb 數據庫建立之后,DBA 可以在其他的數據庫中建立數據對象, 臨時表, 臨時存儲過程, 表變量等會加到 tempdb 中. 在 tempdb 活動很頻繁時, 能夠自動的增長, 因為是 simple 的 recovery model, 會最小化日志記錄, 日志也會不斷的截斷.
3. 如何合理的優化 tempdb 以提高 SQL Server 的性能
如果 SQL Server 對 tempdb 訪問不頻繁,tempdb 對數據庫不會產生影響; 相反如果訪問很頻繁,loading 就會加重,tempdb 的性能就會對整個 DB 產生重要的影響. 優化 tempdb 的性能變的很重要的, 尤其對于大型數據庫.
注: 在優化 tempdb 之前, 請先考慮 tempdb 對 SQL Server 性能產生多大的影響, 評估遇到的問題以及可行性.
3.1 最小化的使用 tempdb
SQL Server 中很多的活動都活發生在 tempdb 中, 所以在某種情況可以減少多對 tempdb 的過度使用, 以提高 SQL Server 的整體性能.
如下有幾處用到 tempdb 的地方:
(1) 用戶建立的臨時表. 如果能夠避免不用, 就盡量避免. 如果使用臨時表儲存大量的數據且頻繁訪問, 考慮添加 index 以增加查詢效率.
(2)Schedule jobs. 如 DBCC CHECKDB 會占用系統較多的資源, 較多的使用 tempdb. 最好在 SQL Server loading 比較輕的時候做.
(3)Cursors. 游標會嚴重影響性能應當盡量避免使用.
(4)CTE(Common Table Expression). 也會在 tempdb 中執行.
(5)SORT_INT_TEMPDB. 建立 index 時會有此選項.
(6)Index online rebuild.
(7) 臨時工作表及中間結果集. 如 JOIN 時產生的.
(8) 排序的結果.
(9)AFTER and INSTEAD OF triggers.
不可能避免使用 tempdb, 如果有 tempdb 的瓶頸或 issue, 就該返回來考慮這些問題了.
3.2 重新分配 tempdb 的空間大小
在 SQL Server 重啟時會自動建立 8MB 大小的 tempdb, 自動增長默認為 10%. 對于小型的數據庫來說,8MB 大小已經足夠了. 但是對于較大型的數據庫來說,8MB 遠遠不能滿足 SQL Server 頻繁活動的需要, 因此會按照 10% 的比例增加, 比如說需要 1GB, 則會需要較長的時間, 此段時間會嚴重影響 SQL Server 的性能. 建議在 SQL Server 啟動時設置 tempdb 的初始化的大小 (如下圖片設置為 MDF:300MB,LDF:50MB), 也可以通過 ALTER DATABASE 來實現. 這樣在 SQL Server 在重啟時 tempdb 就會有足夠多的空間可利用, 從而提高效率.
難點在于找到合理的初始化大小, 在 SQL Server 活動頻繁且 tempdb 不在增長時會是一個合適的值, 可以設置此時的值為 Initial Size; 當然還會有更多的考量, 此為一例.
3.3 不要收縮 tempdb(如沒有必要)
有時候我們會注意到 tempdb 占用很大的空間, 但是可用的空間會比較低時, 會想到 shrink 數據庫來釋放磁盤空間, 此時要小心了, 可能會影響到性能.
如上圖所示:tempdb 分配的空間為 879.44MB, 有 45% 的空間是空閑的, 如果 shrink 掉, 可以釋放掉一部分磁盤空閑, 但是之后 SQL Server 如有大量的操作時,tempdb 空間不夠用, 又會按照 10% 的比例自動增長. 這樣子的話, 所做的 shrink 操作是無效的, 還會增加系統的 loading.
3.4 分派 tempdb 的文件和其他數據文件到不用的 IO 上
tempdb 對 IO 的要求比較高, 最好分配到高 IO 的磁盤上且與其他的數據文件分到不用的磁盤上, 以提高讀寫效率.
tempdb 也分成多個文件, 一般會根據 CPU 來分, 幾個 CPU 就分幾個 tempdb 的數據文件. 多個 tempdb 文件可以提高讀寫效率并且減少 IO 活動的沖突.
關于 Tempdb 對 SQL Server 性能優化有何影響問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注丸趣 TV 行業資訊頻道了解更多相關知識。
