## 認識TEMPDB TEMPDB和其他數據庫一樣以MODEL庫為模板創建，是一個全局資源，可供所有連接到實例的用戶使用，不同的是它在每次SQL Server啟動的時候都會被重新創建，它主要存儲三種對象： * 用戶對象：包括用戶顯示創建的本地/全局臨時表、表變量、表值函數、游標、臨時存儲過程等 * 內部對象：tempdb中具體的內部對象一般是不可見的，因為它們的meta信息只存于內存中并沒有harden，所以系統視圖也查詢不到，大體分為work table、work file、sort units三類 * 版本存儲：包括聯機索引、多活動結果集、after觸發器以及使用快照的隔離級別都會在TEMPDB存儲舊的行版本 ## 常見場景 了解了TEMPDB存儲了哪些東西就可以知道它對于整個實例的重要性，尤其是性能上的影響，一些高并發場景很容易凸顯問題。 比如在生產環境中出現這樣的場景——應用頻繁的創建和銷毀臨時表 ~~~ select * from sys.sysprocesses where spid>50 and lastwaittype<>'MISCELLANEOUS' and status<>'sleeping' and spid<>@@SPID ~~~  500+的session掛起，這時候業務已經無法正常提供服務了，每個session都在等PAGELATCH_UP（lastwaittype）對應資源是 2：*：2（waitresource）。 SQLServer的 PAGELATCH：PAGEPATCH是同步訪問數據庫PAGE的Latch，SQL server的BP里每個數據頁（8K）都有一個對應的LATCH，要訪問某個PAGE必須首先獲得這個PAGE的LATCH，PAGELATCH有很多種，如共享的PAGELATCH_SH,獨占的PAGELATCH_EX，更新的PAGELATCH_UP。 waitresource 2：*：2 分別表示database_id,file_id,page_id 對應資源是tempdb的某個datafile第二個數據頁；了解SQLServer的存儲結構可以知道datafile的前幾個page是固定的系統page，第二個PAGE既是固定的全局分配映射頁（GAM），TEMPDB做統一區分配的時候會用到。 這個場景是因為對臨時表操作的并發過高，TEMPDB在系統頁上出現嚴重爭搶導致整個實例卡慢從而影響業務，是一個非常典型的場景，解決方法最好從TEMPDB的規劃設計入手。 ## 優化建議 * TEMPDB的數據文件和正常業務數據庫分開存儲，配置在不同的物理設備上；日志文件建議和數據文件分開存儲，業務數據庫的日志文件也建議這樣 * 當實例分配的CPU個數小于8時，數據文件的個數調整到和實例綁定CPU個數一致；當分配的CPU個數大于8且存在系統頁的閂鎖爭搶時按照4的倍數增加文件直到瓶頸不在閂鎖上 ~~~ --獲取實例分配的CPU個數 USE [master] GO SELECT COUNT(*) AS CPU_NUM FROM SYS.DM_OS_SCHEDULERS WHERE IS_ONLINE=1 AND CPU_ID<255 GO ~~~ * 數據和日志文件的最大空間根據物理設備大小配置到最大值或者不限 ~~~ --不限文件大小 USE [master] GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', MAXSIZE = UNLIMITED) GO ~~~ * 盡量避免SHRINKING TEMPDB哪怕只是某個FILE ~~~ USE [tempdb] GO DBCC SHRINKFILE (TEMPDEV, 'TARGET SIZE IN MB') ~~~ * 啟用數據和日志文件的自動增長，配置所有數據文件的增長速度一致、初始大小一致；這里還有一種說法是評估設定好應用所需的TEMPDB穩定空間不做自動增長或者靠監控在業務低峰主動調整，當然如果能做到這一點那是很好的，但這需要應用非常穩定并做過長時間測試能夠找準這個穩定空間并做好監控和主動擴容，在實際環境中這基本是很難達到的，所以配置自增長是在實際生產環境中更推薦的做法 * 如果不是物理設備的空間或性能異常請保證只有一個日志文件 * 開啟Trace Flag 1118緩解SGAM頁的爭搶 DBCC TRACEON (1118, -1) * 如果有這樣的場景：PAGELATCH_UP對應資源都是同一個FILE（FILE_ID相同），那么可能是由于數據文件大小非常不均衡使proportional fill algorithm一直在一個文件上分配空間，這種情況可以考慮使用黑科技開啟Trace Flag 1117解決，但同時會造成空間消耗，因為這是實例級別的參數不止作用于TEMPDB，所有DB的數據文件都會統一增長 DBCC TRACEON (1117, -1) ## 空間監控 最后，現實場景中即使對TEMPDB做好了規劃也不排除應用異常使用導致的各種問題，所以合理的監控是必不可少的。 SQLServer針對TEMPDB提供了一些視圖信息方便我們監控排查，主要了解sys.dm_db_file_space_usage、sys.dm_db_session_space_usage、sys.dm_db_task_space_usage這三個就可以幫我們做好監控。 ~~~ --分類獲取TEMPDB總空間使用 Select SUM ( user_object_reserved_page_count)*8 as user_objects_kb, SUM ( internal_object_reserved_page_count)*8 as internal_objects_kb, SUM ( version_store_reserved_page_count)*8 as version_store_kb, SUM ( unallocated_extent_page_count)*8 as freespace_kb From sys .dm_db_file_space_usage --獲取每個會話對TEMPDB用戶對象和內部對象的使用空間 --排查用戶對象和內部對象使用空間異常的問題 select t1.session_id , (t1\. internal_objects_alloc_page_count + task_internal_alloc+t1\. user_objects_alloc_page_count + task_user_alloc)*8 as [SPACE Allocated FOR ALL Objects (in KB)] , (t1\. internal_objects_alloc_page_count + task_internal_alloc )*8 AS [SPACE Allocated FOR Internal Objects (in KB)], (t1\. internal_objects_dealloc_page_count + task_internal_dealloc )*8 as [SPACE Deallocated FOR Internal Objects (in KB)], (t1\. user_objects_alloc_page_count + task_user_alloc )*8 as [SPACE Allocated FOR USER Objects (in KB)], (t1\. user_objects_dealloc_page_count + task_user_dealloc )*8 as [SPACE Deallocated FOR USER Objects (in KB)], DB_NAME( t1.database_id ) AS [ DATABASE NAME ], t3.HOST_NAME AS [ System NAME ] , t3.program_name AS [ Program NAME ] , t3.login_name AS [ USER NAME ], t3.STATUS , t3.cpu_time AS [ CPU TIME(IN milisec) ], t3.total_scheduled_time AS [ Total Scheduled TIME(IN milisec) ], t3.total_elapsed_time AS [ Elapsed TIME(IN milisec) ], (t3\. memory_usage * 8 ) AS [ Memory USAGE (IN KB) ] from sys .dm_db_session_space_usage as t1, ( select session_id, sum(internal_objects_alloc_page_count ) as task_internal_alloc, sum(internal_objects_dealloc_page_count ) as task_internal_dealloc, sum(user_objects_alloc_page_count ) as task_user_alloc, sum(user_objects_dealloc_page_count ) as task_user_dealloc from sys .dm_db_task_space_usage group by session_id) as t2, sys.dm_exec_sessions as t3 where t1\. session_id = t2 .session_id and t2\. session_id = t3 .session_id and t1\. session_id > 50 order by [SPACE Allocated FOR ALL Objects (in KB)] desc --獲取行版本的信息 --排查行版本使用異常的問題 SELECT session_id,elapsed_time_seconds as elapsed_time,* FROM sys.dm_tran_active_snapshot_da ~~~