tempdb對SQL Server資料庫性能有何影響

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重要的一部分,以上隻是對tempdb的一些了解總結,還需要進一步學習