MSSQL如何定位大量占用tempdb的SQL语句
Tags： MSSQLSQL Servertempdb慢SQL
👉 本文共约1131个字，系统预计阅读时间或需5分钟。
简介
有时会收到tempdb日志文件使用率超过90%的告警，最准确的处理方式是直接登上去看有哪些长时间运行且会大量消耗tempdb的语句在执行，但有时刚好不方便操作，在登录查看时sql已经执行完了，事后难判断。
查询文章发现还可以通过tempdb分配情况去分析，不过要注意这种方法找到的只是问题会话，未必能找到导致问题的sql，感觉配合扩展事件的慢sql监控应该能解决这个问题。
查看tempdb记录的分配情况
如果查询上面的DMV距事件发生时间太久，可能就查不到了，要尽快查看
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
有四个关键信息：
- session_id：可以查询该session的相关信息
- internal_objects_alloc_page_count：分配给session内部对象的数据页
- internal_objects_dealloc_page_count：已经释放的数据页
- login_name：该session的登录名
- 从internal_objects_alloc_page_count可以看出，给session分配了133200页，计算一下大约是1G，假如和告警时tempdb增长的大小一致，执行时间也接近，基本就可以断定是这个session引起的。1select 133200*8/1024/1024 as [size_GB]
- 从internal_objects_dealloc_page_count可以看到占用tempdb的数据已经释放了。
- 从login_name可知道操作人（这就是实名用户的好处之一，可以很快追踪使用者，是内部人员操作）。
- 从session_id可以查询最后一条执行的语句。
- 从internal_objects_alloc_page_count可以看出，给session分配了133200页，计算一下大约是1G，假如和告警时tempdb增长的大小一致，执行时间也接近，基本就可以断定是这个session引起的。
1 2 3 4 | 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)
1 2 3 4 5 6 7 | -- 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用户对象和内部对象的使用空间
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | -- 排查用户对象和内部对象使用空间异常的问题 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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/Hehuyi_In/article/details/103149146?spm=1001.2014.3001.5502
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/
