sqlserver 定位大量占用tempdb的sql语句

有时会收到tempdb日志文件使用率超过90%的告警，最准确的处理方式是直接登上去看有哪些长时间运行且会大量消耗tempdb的语句在执行，但有时刚好不方便操作，在登录查看时sql已经执行完了，事后难判断。

查询文章发现还可以通过tempdb分配情况去分析，不过要注意这种方法找到的只是问题会话，未必能找到导致问题的sql，感觉配合扩展事件的慢sql监控应该能解决这个问题。

查看tempdb记录的分配情况：如果查询上面的DMV距事件发生时间太久，可能就查不到了，要尽快查看

use tempdb
go
SELECT top 10 t1.session_id,                                                   
t1.internal_objects_alloc_page_count,  t1.user_objects_alloc_page_count,
t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,
t3.login_name,t3.login_time,t3.last_request_start_time,t3.status,t3.total_elapsed_time
from sys.dm_db_session_space_usage  t1
inner join sys.dm_exec_sessions as t3
on t1.session_id = t3.session_id
where (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)
order by t1.internal_objects_alloc_page_count desc

有四个关键信息：

• session_id：可以查询该session的相关信息
• internal_objects_alloc_page_count：分配给session内部对象的数据页
• internal_objects_dealloc_page_count：已经释放的数据页
• login_name：该session的登录名

1. 从internal_objects_alloc_page_count可以看出，给session分配了133200页，计算一下大约是1G，假如和告警时tempdb增长的大小一致，执行时间也接近，基本就可以断定是这个session引起的。

select 133200*8/1024/1024 as [size_GB]

2. 从internal_objects_dealloc_page_count可以看到占用tempdb的数据已经释放了。

3. 从login_name可知道操作人（这就是实名用户的好处之一，可以很快追踪使用者，是内部人员操作）。

4. 从session_id可以查询最后一条执行的语句。

select p.*,s.text
from master.dbo.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) s
where spid = 647

最后查出来这是一个select into的语句

另外补充两个tempdb相关sql

tempdb空间使用跟踪（不要用sp_spaceused）

--Tempdb库文件的使用情况
SELECT SUM(user_object_reserved_page_count) * 8 as usr_obj_kb,
       SUM(internal_object_reserved_page_count) * 8 as internal_obj_kb,
       SUM(version_store_reserved_page_count) * 8 as version_store_kb,
       SUM(unallocated_extent_page_count) * 8 as freespace_kb,
       SUM(mixed_extent_page_count) * 8 as mixedextent_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;

--会话使用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;

查占用 temp db的sql

SELECT es.host_name , es.login_name , es.program_name,
st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,
SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset 
END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,tsu.request_id, tsu.exec_context_id, 
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes, 
er.logical_reads, er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu inner join sys.dm_exec_requests er 
 ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id) 
inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id ) 
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) 
DESC

参考

https://blog.csdn.net/kk185800961/article/details/43530153

SQLServer · 最佳实践 · TEMPDB的设计

Track SQL Server TempDB Space Usage

https://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/