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使用率的高情况，能给出的建议如下：
- 尽量避免使用触发器，使用触发器时相关的操作尽可能的小。
- 如果内存资源充足，可以使用CTE取代表变量。
- 表和索引的排序尽量设计的合理，避免大量的临时排序。
- 适当放大Tempdb的文件的大小，并将增长模式改为按照固定大小。
