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的文件的大小，并将增长模式改为按照固定大小。