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活動的沖突.
