SQL server 檢視什麼語句在使用臨時表

SQL server 查詢那些語句在使用臨時表

最近在日常的性能測試工作中發現，資料庫端的IO讀寫比較大，有規律的2-8M的波動，資料庫的版本為 SQL server 2008 sp3。

這些IO操作主要來自于臨時表，在測試之前，我們已經對部分消耗資源較多的語句進行了跟蹤。

對一些使用臨時表的語句進行了修改，但是肯定還是有落網之魚。我們需要對其進行甄别，做到進一步的優化。

在開始之前，我們先來了解下什麼樣的操作會使用到臨時表：

• 時的使用者對象， 比如臨時表，表變量等（#，##，@開頭的一些變量）
• 遊标
• 内部中的一些列印和排序操作
• 用于快照隔離的行版本控制機制
• 線上的索引重建操作
• 啟用MARS (Multiple Active Resultsets) 機制或者操作
• 觸發器

以上的一些機制或者操作，會使用到系統的臨時表空間。

我們可以使用下面的語句擷取與臨時表相關的一些資訊：

1. 查詢臨時資料庫中還有多少資源空餘：

SELECT   SUM(unallocated_extent_page_count) AS [Temp Free Pages], 
        (SUM(unallocated_extent_page_count)*1.0/128) AS [Temp Free space in MB] 
FROM sys.dm_db_file_space_usage;      

2. 查詢TempDB中有多少資源用版本控制的

SELECT  SUM(version_store_reserved_page_count) AS [TempDB version store pages used], 
        (SUM(version_store_reserved_page_count)*1.0/128) AS [TempDB version store space in MB] 
FROM sys.dm_db_file_space_usage;      

3. 查詢TempDB中有多少資源是被内部對象使用的：

SELECT   SUM(internal_object_reserved_page_count) AS [TempDB internal object pages used], 
        (SUM(internal_object_reserved_page_count)*1.0/128) AS [TempDB internal object space in MB] 
FROM sys.dm_db_file_space_usage;      

4. 查詢TempDB中有多少資源是被使用者級别的對象使用的：

SELECT SUM(user_object_reserved_page_count) AS [TempDB user object pages used], 
　　　　(SUM(user_object_reserved_page_count)*1.0/128) AS [TempDB user object space in MB] 
FROM sys.dm_db_file_space_usage;      

在上面的語句中，我們将查詢出來的page除以128來擷取值，那是因為SQL server 每個page的大小是8K，那麼除以128就可以得到機關是M的資料值。

我們可以結合以下幾張系統管理表，擷取目前session使用TempDB 情況：

• dm_db_file_space_usage – 傳回tempdb中檔案的空間使用情況
• dm_db_session_space_usage – 傳回每個會話配置設定和釋放配置設定的頁數
• dm_db_task_space_usage – 傳回任務頁面配置設定和釋放活動

通過該語句我們可以看到tempdb的整體的資源配置設定情況。

SELECT    ssu.session_id,
        ssu.internal_objects_alloc_page_count,
        ssu.user_objects_alloc_page_count,
        ssu.internal_objects_dealloc_page_count ,
        ssu.user_objects_dealloc_page_count,
        es.*
FROM    sys.dm_db_session_space_usage  ssu ,sys.dm_exec_sessions as es 
WHERE   ssu.session_id = es.session_id
AND     (ssu.internal_objects_alloc_page_count>0
OR      ssu.user_objects_alloc_page_count>0
OR      ssu.internal_objects_dealloc_page_count>0
OR      ssu.user_objects_dealloc_page_count>0)      

我們可以通過以下的語句，得到目前正在使用Tempdb的session的SQL語句：

SELECT    ssu.session_id,                             
        st.text
FROM    sys.dm_db_session_space_usage as ssu,
        sys.dm_exec_requests as er
CROSS APPLY    sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE   ssu.session_id = er.session_id
AND        ssu.session_id > 0
AND        (ssu.internal_objects_alloc_page_count > 0
OR        ssu.user_objects_alloc_page_count > 0
OR        ssu.internal_objects_dealloc_page_count > 0
OR        ssu.user_objects_dealloc_page_count > 0)      

我們可以通過下面的語句，來擷取目前有哪些session正在使用tempdb，以及一些其他的資訊：

SELECT
  sys.dm_exec_sessions.session_id AS [SESSION ID]
  ,DB_NAME(database_id) AS [DATABASE Name]
  ,HOST_NAME AS [System Name]
  ,program_name AS [Program Name]
  ,login_name AS [USER Name]
  ,status
  ,cpu_time AS [CPU TIME (in milisec)]
  ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
  ,total_elapsed_time AS    [Elapsed TIME (in milisec)]
  ,(memory_usage * 8)      AS [Memory USAGE (in KB)]
  ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
  ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
  ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
  ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
  ,CASE is_user_process
             WHEN 1      THEN 'user session'
             WHEN 0      THEN 'system session'
  END         AS [SESSION Type], row_count AS [ROW COUNT]
FROM 
  sys.dm_db_session_space_usage
INNER join
  sys.dm_exec_sessions
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
ORDER BY status ASC      

最後在網上收集到一個外國牛人寫的SQL語句，結合資源使用的情況，更加的全面：Reference: Pinal Dave (https://blog.sqlauthority.com 

SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) >= 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC      

希望以上的資訊能夠幫助到大家，對于tempdb使用率的高情況，能給出的建議如下：

1. 盡量避免使用觸發器，使用觸發器時相關的操作盡可能的小。
2. 如果記憶體資源充足，可以使用CTE取代表變量。
3. 表和索引的排序盡量設計的合理，避免大量的臨時排序。
4. 适當放大Tempdb的檔案的大小，并将增長模式改為按照固定大小。