合 MSSQL如何定位大量占用tempdb的SQL语句
Tags： MSSQLSQL Server慢SQLtempdb
👉 本文共约1192个字，系统预计阅读时间或需5分钟。
目录
简介
若SQL在运行，则直接去查询会话即可。
若SQL已完成，则可以通过sys.dm_db_session_space_usage查询，但是时间长也不会保存
若2017版本，则可以使用查询存储来获取，若其它版本可以通过JOB定时来获取。
查看tempdb记录的分配情况
如果查询上面的DMV距事件发生时间太久，可能就查不到了，要尽快查看
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 | 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 SELECT top 100 t1.session_id, t1.internal_objects_alloc_page_count*8/1024 internal_objects_alloc_mb, t1.user_objects_alloc_page_count*8/1024 user_objects_alloc_mb, t1.internal_objects_dealloc_page_count*8/1024 internal_objects_dealloc_mb , t1.user_objects_dealloc_page_count*8/1024 user_objects_dealloc_mb, ((t1.internal_objects_alloc_page_count+t1.user_objects_alloc_page_count)- (t1.internal_objects_dealloc_page_count+t1.user_objects_dealloc_page_count))*8/1024 now_tempdb_mb, 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) and t3.status not in ('sleeping') order by (t1.internal_objects_alloc_page_count+t1.user_objects_alloc_page_count)- (t1.internal_objects_dealloc_page_count+t1.user_objects_dealloc_page_count) desc, (t1.internal_objects_alloc_page_count+t1.user_objects_alloc_page_count) desc |
有四个关键信息：
- 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 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
