sql server 臨時表(中) Tempdb監控

 一. 監控概述

　　Tempdb庫空間使用的一大特點，是只有一部分對象，例如用戶創建的臨時表、table變數等，可以用sys.allocation_units和sys.partitions這樣的管理視圖來管理，許多內部對象和版本存儲在這些管理視圖中沒有體現，所以sp_spaceused的結果和真實的使用情況會有很大差異，tempdb的空間使用是不能用sp_spaceused來跟蹤的。使用dbcc showfilestats 可以反映數據文件使用的整體情況，但不能監控到空間被誰以什麼樣的方式用掉。

　　對於Tempdb的磁碟空間不足，可能會嚴重破壞 SQL Server 生產環境，並會使正在運行的應用程式無法完成操作。可以使用 sys.dm_db_file_space_usage 動態管理視圖來監視 tempdb 文件中的這些功能使用的磁碟空間。此外，若要在會話級或任務級監視 tempdb 中的頁分配或頁釋放活動，可以使用動態管理視圖 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage。這些視圖可用於標識使用 tempdb 中大量磁碟空間的大型查詢、臨時表或表變數。還可以使用若幹個windows性能計數器來監視 tempdb 中的可用空間以及使用 tempdb 的資源。

　　(1)使用sys.dm_db_file_space_usage 視圖能反映tempdb在幾個大類里的空間使用分佈， 使用sys.dm_db_session_space_usage 視圖能反映tempdb在各會話中空間使用分佈。(2)監控磁碟I/O來確認是否有瓶頸。(3)監視TempDB相關的DDL。

二. 監視tempdb空間分佈

　　2.1 sys.dm_db_file_space_usage視圖

　　　　使用該視圖可以：返回資料庫中每個文件的空間使用信息。下麵例出該視圖的主要信息,詳細視圖查看msdn。通過這個視圖就能知道tempdb的空間是被哪一塊對象使用掉的，是用戶對象，還是內部對象，還是版本存儲。

　　2.2 sys.dm_db_session_space_usage視圖 

　　　　使用該視圖，能返回資料庫每個會話分配和釋放的頁數。此視圖僅適用於tempdb資料庫。只有在任務結束時才更新計數器; 統計不反映正在運行的任務。

　　2.3 監視的sql腳本　　

　　　　對於監視tempdb三類對象空間分佈，這裡使用比較"輕量極"對系統影響不大的方法。通過間隔時間運行來監視空間使用，監視包括 DBCC,DMV,DMF 等，把結果輸出到一個文件或資料庫表裡。

-- 間隔每秒執行一次，手工終止或加入作業
use tempdb
while 1=1
BEGIN

     select GETDATE() AS '當前時間'
     
     --------------query1 從文件級查看tempdb使用情況------------------
     /*
       TotalExtents 是磁碟占用的空間,一個extent為64k
       UsedExtents  是磁碟使用的空間
     */
     dbcc showfilestats
     
     ------------- query2 查看當前Tempdb各對象，占用總空間-------------
     /*
      user_object_reserved_page_count 用戶對象包括： 臨時表,表變數,表值函數中返回的表,定義的表和索引
      internal_object_reserved_page_count 內部對象包括：排序段,哈希聯接,游標
      version_store_reserved_page_count 行版本包括： 觸發器,運行聯機索引,快照隔離級別或使用行版本控制
      unallocated_extent_page_count:未分配空間(可用空間)
    */
      select  'Tempdb' as DB, GETDATE() as  [Time],
              SUM(user_object_reserved_page_count)*8.0 as  '用戶對象占用總空間_kb',
              SUM(internal_object_reserved_page_count)*8.0 as '內部對象占用總空間_kb',
              SUM(version_store_reserved_page_count) * 8.0 as '行版本占用總空間_kb',
              SUM(unallocated_extent_page_count) *8.0 as '未分配總空間_kb'
       from sys.dm_db_file_space_usage
         where database_id=2 
  
     -----------------query3 查詢tempdb各會話，占用和釋放的空間-------------
     SELECT 
        t1.session_id,
        (t1.internal_objects_alloc_page_count * 8.0) AS internal_objects_alloc_kb,
        (t1.user_objects_alloc_page_count * 8.0)AS user_objects_alloc_kb,
        (t1.internal_objects_dealloc_page_count* 8.0) AS internal_objects_dealloc_kb,
        (t1.user_objects_dealloc_page_count * 8.0)  AS user_objects_dealloc_kb
    from sys.dm_db_session_space_usage as t1, 
    sys.dm_exec_sessions as t3 
    where t1.session_id = t3.session_id 
    and (t1.internal_objects_alloc_page_count >0
    or t1.user_objects_alloc_page_count>0
    or t1.internal_objects_dealloc_page_count>0
    or t1.user_objects_dealloc_page_count>0)
    and t1.session_id <>@@SPID
    
     -----------------query4 查詢tempdb 各會話以及sql語句，占用空間-------------
    /*
     會話的內部對象和用戶對象的保留或釋放的頁數
    */
    SELECT t1.session_id,  
    (t1.internal_objects_alloc_page_count * 8.0) AS internal_objects_alloc_kb,
    (t1.user_objects_alloc_page_count * 8.0)AS user_objects_alloc_kb,
    (t1.internal_objects_dealloc_page_count* 8.0) AS internal_objects_dealloc_kb,
    (t1.user_objects_dealloc_page_count * 8.0)  AS user_objects_dealloc_kb,                                                
    st.text                                                        
    from sys.dm_db_session_space_usage as t1,                               
    sys.dm_exec_requests as t4                                              
    CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st                   
     where  t1.session_id = t4.session_id                                       
       and t1.session_id >50                                                
    and (t1.internal_objects_alloc_page_count>0
    or t1.user_objects_alloc_page_count >0
    or t1.internal_objects_dealloc_page_count>0
    or t1.user_objects_dealloc_page_count>0) 
    and t1.session_id <>@@SPID

END

　　監聽如下圖所示：

 　　2.4  行版本監控

　　　　　　行版本是可以跨會話的，所以在sys.dm_db_session_space_usage中只有內部對象和用戶對象所占用的空間查看。如果要監聽和回收這部分空間，可使用下麵腳本：

--(1)另外查詢行版本運行最長的事務
select top 2 
    transaction_id, 
    transaction_sequence_num, 
    elapsed_time_seconds 
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds DESC

--(2)根據得到當前會話
select   session_id from sys.dm_tran_session_transactions where transaction_id=@transaction_id

三. 監視TempDB的I/O磁碟

　　由於TempDB被多個地方廣泛使用，有可能造成磁碟的消耗較大，成為I/O瓶頸。除了監視windows性能計數器,還有就是DMV視圖。

　　3.1 對於windows性能計數器主要包括三個

-- 每次數據傳輸的平均時間
AVG.Disk sec/Transfer
--磁碟讀數據所需的平均時間
AVG.Disk sec/Read
--磁碟寫數據所需的平均時間
AVG.Disk sec/Write

　　3.2 DMV視圖

　　　　下麵使用sys.dm_io_virtual_file_stats來確認IO瓶頸，它返回數據文件和日誌文件的 I/O 統計信息，包括對文件發出的讀取/寫入次數以及總位元組數， file_id欄位中1 是數據文件，2是日誌文件，腳本如下：

SELECT DB_NAME(database_id) AS 'DBName',
file_id,
io_stall_read_ms/num_of_reads AS 'Avg Read ms/Transfer',
io_stall_write_ms/num_of_writes AS 'Avg Write ms/Transfer'
 FROM sys.dm_io_virtual_file_stats(NULL,null)
WHERE  database_id=2

　　下麵是數據文件及日誌文件性能指標建議列表，僅供參考

四 監視TempDB相關的DDL

　　DDL主要是用在定義或改變表的結構，數據類型，表之間的鏈接和約束等初始化工作上。對於大量，頻繁地創建和刪除臨時表和表變數會引起元數據上的爭用。在2008版本中在一定條件下局部臨時表和表變數會被緩存，以減少元數據的爭用。通過windows性能計數器或sys.dm_os_waiting_tasks 視圖可以周期性的檢查等待時間較長的會話。

　　4.1 sys.dm_os_waiting_tasks 

　　　　使用該視圖返回有關正在等待某些資源的任務的等待隊列的信息。腳本如下

SELECT session_id,wait_duration_ms,resource_description,GETDATE()AS '當前時間'
FROM sys.dm_os_waiting_tasks 
WHERE resource_description LIKE '2:%' AND wait_type LIKE 'PAGE%LATCH_%'
ORDER BY wait_duration_ms desc

　　4.2 windows性能計數器

SQL Server:Access Methods\Workfiles Created/Sec
SQL Server:Access Methods\Worktables Created/Sec
SQL Server:Access Methods\Mixed Page Allocations/Sec
SQL Server:Access Methods\Temp Tables Created/Sec
SQL Server:Access Methods\Temp Tables for destruction

　　最後還可參考msdn文檔：解決 tempdb 中磁碟空間不足的問題