TempDB 101 – 資校庫效能的關鍵
這篇文章主要說明 TempDB 在 SQL Server 所扮演的角色、對於整體效能的影響以及效能建議的設定。
磁碟效能瓶頸
因為資料庫資料對於磁碟資料的寫入原則上分為兩大類，
- 一類是使用者資料的寫入，這類的資料SQL Server Engine 會透過 “非同步”的方式寫入到磁碟，附檔名為 *.MDF 。”非同步”寫入的意義是：資料庫會累積到一段時間後，才會整批寫入磁碟檔案。並不會立刻就同步寫入。
- 另一類的資料寫入是Transaction Log (*.LDF)，這類的寫入是”同步’寫入。也就是說每一筆資料的更新，SQL 必須要記錄到 Transaction Log 之後才會繼續接下來的動作。因為一定要先記錄 Transaction Log也是因為要達到 Rollback 的目的。因此，寫入 Transaction Log 的所在磁碟速度的快慢，也會對於SQL Server 整體的效能有直接的影響。
為什麼 TempDB會變成瓶頸?
原因主要在於在一些特定的”動作”是需要經過 TempDB ，這些”動作” 一定要等到TempDB處理完之後才會繼續進行，
因此 TempDB 處理的效能就會造成這些”動作”的瓶頸，所以下一個問題是，那麼到底有哪些”動作”會跟TempDB有關呢?
另外一個原因，TempDB 只有一個!! Yes. 所有的 database 都會共用這個 TempDB，因此相互等待的問題就會出現。
TempDB 負責什麼?
MS SQL Server 中，tempDB 主要負責的角色有三大類型
- User Objects: 例如 System Tables, Indexes, 使用者定義的Temp Tables/indexes或是當記憶體不足時，就會轉向跟TempDB暫存。
- Internal Objects: 執行DBCC checkDB時所需要的暫存資料、Cursor、Hash Join、或是因為查詢語句中 Group by, Oder By, Union，sort，distinct運算所需要的暫存空間、Rebuild Index
- Version Stores: 因為 isolation level (例如: read committed、snapshot) 所需要的暫存空間。因為資料修改需求，如Trigger, Online Index Operations等
常見的情況，針對 Query SQL Statement 來說，如果該 Query 語句中有用到 Group by, Oder By, Union，sort，distinct，就會需要用到 TempDB。
因為 SQL database 在查詢出資料完之後，需要做一些”整理”才有辦法回傳出結果，這個暫時整理的地方就是 TempDB
| Select NameFrom CustomerGroup by Name |
TempDB 的一些特性
TempDB有一些特性，這邊列舉說明：
- TempDB 既然稱為”暫存”，所以會在每次SQL Server 服務 stop/start 的時候被清空
- 預設TempDB 會有8MB MDF 與1 MB LDF。(什麼是MDF, LDF ? 參考上述說明)
- 預設MDF 會自動 10% 的檔案大小成長。
- 預設每個 database instance 只會有一個 TempDB。(這也是效能瓶頸的主要原因之一)
- TempDB 可以由多個實體檔案所組成。(將實體檔案分散在不同的”實體”硬碟，這也是校能條件會比較建議的部分。)
- TempDB 只會使用 Simple Recovery Mode. 很多的設定不能夠讓使用者修改。因為畢竟 TempDB 會SQL Server 用的暫存工作DB，不是使用者 Database。
- TempDB 不像其他使用者Database 一般，無法被backup, restore, attach等
- TempDB 對於Transaction Logs 原則上來說，會用最少的 logs 來記錄。每一次SQL Server checkpoint 時，該TempDB的trans logs 會被刪除。
TempDB遇到效能問題的種類
那麼TempDB 會導致怎樣的問題呢? 可以分為三大類
-
TempDB 問題一
遇到磁碟上存取的 Disk I/O 效能瓶頸。這也是比較常見的問題。如果該磁碟有其他應用系統讀取，
或是磁碟效能比較慢的時候，就會反應到SQL Server 效能。
-
TempDB 問題二
因為 TempDB 只有一個，當所有的Database 都需要使用TempDB 時，只能循序等待。所以會產生競爭等待使用 TempDB 的問題。
-
TempDB問題三:
磁碟空間不足。這類的問題 Error code 多半是 1101, 1105, 3959, 3967。另外有可能會有異常3958 or 3966
如何觀察 TempDB 磁碟效能瓶頸?
- 可以利用 Performance monitor 觀察 tempDB 所在的磁碟讀取的效能狀況
- Avg Disk Sec/Read 與 Avg Disk Sec/Write 這兩個指標
- 一般來說 10~20 ms 是比較正常的讀取效能。如果超過 50-100ms 或是甚至超過 100 ms 表示該磁碟讀取效能有嚴重的問題
- 另外可以透過這兩個 counters 記錄 baseline。Worktables Created/sec 。Workfiles Created/sec
如何觀察 TempDB是否因為許多Database競爭讀取?
可以利用下列的 SQL Query 查詢，
這個 Query 就會列出，目前因為等待 TempDB 的關係等多久 wait_duration_ms?
| Select session_id As SessionID, wait_duration_ms, resource_description From sys.dm_os_waiting_tasks Where wait_type like ‘pagelatch_%’ And (resource_description like ‘2:%:1’ Or resource_description Like ‘2:%:2’ Or resource_description Like ‘2:%:3’) |
誰用最多 TempDB?
我們透過這個系統資訊得知 sys.dm_db_file_space_usage
查詢結果是 # of Page ，一個 Page 為 8K，因此可以乘以 8 就會得知該空間大小
| select sum user_object_reserved_page_count sum internal_object_reserved_page_count sum version_store_reserved_page_count from sys.dm_db_file_space_usage |
TempDB 設定上一般效能建議
- 盡量少用到 tempDB。因為 TempDB 只有一個，而且是所有的資料庫共用競爭使用，因此如果可以在Query 語句中減少或是降低會使用到TempDB的語句就可以避免。
- 增加記憶體。增加記憶體有助於減少TempDB的使用。因為當記憶體不足的時候，資料庫就會轉向求助於TempDB.
- 將TempDB放置在 disk IO較快的硬碟上。建議 RAID 1 or RAID 10。因為 RAID 5 會帶來比較多額外的磁碟讀寫的負荷。
- 預先配置TempDB 較多的磁碟空間。這個目的主要是避免讓tempDB經常性的需要重新配置磁碟空間所帶來的額外磁碟運作。
- 盡量不要做 Shrink DB的動作
- 將TempDB 切割為等份的實體檔案。並且將這些檔案放在不同的實體硬碟。因為這可以讓硬碟可以對這些檔案做同時讀取的動作。
避免使用TempDB?
怎麼盡量避免使用TempDB呢? 這部分一般來說的通則是讓Query 執行更有效率才思考。例如：
- 不要回傳過多不需要的欄位資訊
- 如果可以的話，Sort 可以在 client 端執行，或是選擇適當的 Clustered index
- 避免使用 Union or select Distinct
- 避免使用 temp Table。如果真的需要，可以考慮建立一個使用者 Table。
- 避免使用過大的資料型態，例如nvarchar
- 避免使用 Trigger
- 避免使用 row-versioning transaction
- 避免使用cursors
- 需要 re-build index 的時候，盡量在離峰時間
- 執行 DBCC checkDB的時候，盡量安排在離峰時間
當然，這些情境可以避免使用到 tempDB，並不是一定不能使用
另外一個角度是，可以增加TempDB 內暫存物件的重複被使用性，例如使用 Stored procedure
增加記憶體會幫助TempDB嗎?
資料庫原則上會把所需要的資料與執行計畫存放在 Buffer Cache
當記憶體的 Buffer Cache 不足夠的時候，就會跟 TempDB 要儲存空間
換句話說，當有記憶體效能瓶頸的時候，增加記憶體才有助於整體的效能
如果效能瓶頸不是在記憶體的時候，增加再多的記憶體都不會對 TempDB效能有所幫助
Auto Statistics on
預設 Auto Update/Create Statistics 為 On，保留預設值
因為SQL Server 可以根據這些統計資訊，對於 Query 做出比較好的判斷
預先配置 TempDB 空間大小
可以透過下列指令，修改 TempDB 的空間大小
因為預設為 8MB，每次自動成長 10%。每一次自動成長時，也會帶來相關的磁碟額外的 overhead.
但是，下一個問題是，TempDB 大小怎樣才適合呢? 其實沒有一定
如果有專門的硬碟，是專門存放TempDB，那麼大可以設定該硬碟的 50~80%空間給 tempDB使用
但是多半的情況，我們沒有專門一顆硬碟給 TempDB，這種情況下，其實要經過一些觀察，
透過上述說的 performance monitor counter 觀察TempDB的大小量來建立一個基準
| Use Master; goAlter Database TempDB Modify File (name = tempdev, size = 30MB); GoAlter Database TempDB Modify File (Name = tempLog, Size = 10MB)Go |
不要試著Shrink TempDB 大小
在某些狀況下TempDB或許會成長的很大，通長時間的經過，會慢慢恢復正常的大小，這些都是正常的狀況
因為Shrink TempDB會造成過多額外的磁碟與運作的 overhead，
如果真的要讓 tempDB空間變小的話，最有效率的方式反而是重新啟動 SQL Server service
TempDB的資料將在下一次 SQL Server重新被啟動的時候被清空
將TempDB切割為多份等份的檔案
這邊主要要切割多份等份的檔案為 MDF.
為什麼要等份的檔案大小呢? 因為SQL Server 會根據檔案大小來決定平均來說要用哪一個 tempDB 檔案，
如果有個檔案特別大的話，那麼那個檔案會被使用到的機率相對會比較大。
但是，我們比較希望的是，TempDB檔案的使用是可以被平均分散在這些不同的檔案。
所以，Auto growth 的比率，當然也要設定為相同。確定每個檔案的大小都會相同。
另外，建議存放在不同的實體磁碟，因為這樣每個磁碟的讀取就可以同步進行。讀取這些檔案。
要切割多少 TempDB檔案?
原則上，如果該電腦為 8 核心的 CPU，建議可以將TempDB檔案切割 4~8個等份。
主要原因是因為，我們希望平行處理所能帶來一些效能上的幫助。
希望這篇文章讓您對 TempDB有更深一層的了解。
微軟官方參考資料
https://msdn.microsoft.com/zh-tw/library/ms190768.aspx
https://technet.microsoft.com/zh-tw/library/ms175527(v=sql.105).aspx