八、IO最佳化（6）最佳化tempdb效能

一、tempdb簡介

1. 概念

　　tempdb是一個系統資料庫，可供串連到SQL Server執行個體的所有使用者使用。

　　每次啟動 SQL Server 時都會重新建立 tempdb，從而在系統啟動時總是保持一個乾淨的資料庫副本。在斷開聯結時會自動刪除暫存資料表和預存程序，並且在系統關閉後沒有活動串連。因此 tempdb 中不會有什麼內容從一個 SQL Server 會話儲存到另一個會話。

　　不允許對 tempdb 進行備份與還原操作。

2. tempdb儲存的對象

1）顯式建立的使用者物件

　　這些對象由使用者顯式建立。存在於使用者會話的範圍中，也可位於建立對象所用的常式預存程序、觸發器或使用者定義函數）的範圍中。

　　包括：表和索引系統的，或使用者定義的）、暫存資料表和索引全域的，或局部的）、表變數、資料表值函式中返回的表。

2）資料庫引擎建立的內部對象

　　這些內部對象由資料庫引擎根據需要而建立，用於處理SQL Server語句。可以在語句的範圍中建立和刪除。每個內部對象至少使用9個頁面：1個IAM頁，1個連續8頁的區。

　　包括：用於遊標或假離線操作以及臨時大型物件LOB）儲存的工作表；用於HASH串連或HASH彙總操作的工作表；用於建立或重建索引等操作如果指定了SORT_IN_TEMPDB）的中間排序結果，或者某些GROUP BY、ORDER BY或UNION查詢的中間排序結果。

3）版本儲存區

　　版本儲存區是資料頁的集合，它包含支援使用資料列版本設定的功能所需的資料行，主要用來支援快照交易隔離等級，以及一些其它提高資料庫並發效能的新功能。主要分為2類：公用版本儲存區、聯機索引產生版本儲存區。

　　包括：由使用快照隔離等級或已提交隔離等級基於資料列版本設定）的資料庫中的資料修改事務產生的行版本；由資料修改事務為實現聯機索引操作、多個活動的結果集MARS）以及AFTER觸發器等功能而產生的行版本。

3. tempdb 的效能提高

　　在 SQL Server 中，tempdb 效能以下列方式進行提高：

1）可能緩衝暫存資料表和表變數。緩衝允許刪除和建立臨時對象的操作非常快速地執行，並減少頁分配的爭用問題。

2）分配頁閂鎖協議得到改善。從而減少使用的 UP更新）閂鎖數。

3）減少了 tempdb 的日誌開銷。從而降低了 tempdb 記錄檔上的磁碟 I/O 頻寬佔用。

4）在 tempdb 中分配混合頁的演算法得到改善。

二、監視tempdb大小

　　tempdb 資料庫的大小和物理位置可能會影響系統的效能。tempdb 的大小可以影響系統效能。例如，如果 tempdb 的大小太小，則每次啟動 SQL Server 時，系統處理可能忙於資料庫的自動成長，而不能支援工作負載要求。可以通過增加 tempdb 的大小來避免此開銷。

　　如果 tempdb 中磁碟空間不足，則可能會嚴重破壞 SQL Server 生產環境，並會使正在啟動並執行應用程式無法完成操作。

　　可以使用 sys.dm_db_file_space_usage 動態管理檢視來監視 tempdb 檔案中的這些功能使用的磁碟空間。

　　此外，若要在會話級或任務級監視 tempdb 中的頁分配或頁釋放活動，可以使用動態管理檢視 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage。這些視圖可用於標識使用 tempdb 中大量磁碟空間的大型查詢、暫存資料表或表變數。

　　還可以使用若干個計數器來監視 tempdb 中的可用空間以及使用 tempdb 的資源。

http://technet.microsoft.com/zh-cn/library/ms176029(v=sql.105).aspx

　　每次建立 tempdb 時都要使用檔案大小和檔案增長參數的值。例如，如果您將 tempdb 資料檔案的大小增加到 20 MB 並將檔案增量增加到 15%，則新的值將立即生效。如果後續事務活動使 tempdb 的大小增大，則每次重新啟動 SQL Server 執行個體時，資料檔案的大小都將返回到 20 MB。

三、tempdb的通用原則

1. 檔案數量

　　根據需要建立足夠多的檔案以使磁碟寬度最大化。使用多個檔案可以減少 tempdb 儲存爭用並獲得更大的延展性。但是，請勿建立過多的檔案，因為此操作可能降低效能並增加管理開銷。

　　作為通用原則，為伺服器中的每一個 CPU 建立一個資料檔案用於解釋任何親和性遮罩設定），然後根據需要上下調整檔案的數量。

2. 復原模式

　　將 tempdb 的復原模式設定為 SIMPLE。此模式自動回收日誌空間以保持較小的空間要求。

3. 自動成長

　　通過將檔案大小設定為足夠容納環境中典型工作負載的值來預分配所有 tempdb 檔案的空間。這可以避免 tempdb 因擴充得過於頻繁而影響效能。

本文結語：

　　規劃tempdb資料庫的大小，根據CPU數量使用多個檔案。

本文出自 “我們一起追過的MSSQL” 部落格，轉載請與作者聯絡！