Sqlserver定位哪些物件和哪些會話哪些sql語句消耗了tempdb

SELECT SUM(user_object_reserved_page_count) * 8/1024/1024 as user_object_size_GB,
       SUM(internal_object_reserved_page_count) * 8/1024/1024 as internal_object_size_GB,
       SUM(unallocated_extent_page_count) * 8/1024/1024 as unallocated_size_GB,
       SUM(allocated_extent_page_count) * 8/1024/1024 as allocated_size_GB,
       SUM(mixed_extent_page_count) * 8/1024/1024 as mixed_size_GB,
       SUM(version_store_reserved_page_count) * 8/1024/1024 as version_store_size_GB,
       SUM(total_page_count) * 8/1024/1024 as total_size_GB
  FROM tempdb.sys.dm_db_file_space_usage;
--查詢tempdb的tempfile的使用情況
--user_object_reserved_page_count表示：全域性臨時表和索引、區域性臨時表和索引、表變數
--internal_object_reserved_page_count表示：遊標、臨時大型物件 (LOB) 、雜湊聯接、排序等操作
--version_store_reserved_page_count表示:版本儲存


use tempdb
go
SELECT top 100 t1.session_id, 'KILL '+CAST(t1.session_id AS NVARCHAR(100)) AS KillCmd ,                                                 
t1.internal_objects_alloc_page_count,t1.user_objects_alloc_page_count, t3.host_name,t3.login_name,t3.login_time,
(t1.internal_objects_alloc_page_count-t1.internal_objects_dealloc_page_count)*8/1024/1024 internal_nodealloc_GB ,
(t1.user_objects_alloc_page_count-t1.user_objects_dealloc_page_count)*8/1024/1024 user_nodealloc_GB ,
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-t1.internal_objects_dealloc_page_count desc
--查詢消耗tempdb的session資訊

select Replace(b.text,'''','''') ,a.*
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b where a.spid in (XX)
--查詢session中執行的sql語句，XX就是第一個語句結果中的第一列t1.session_id


SELECT a.session_id, a.transaction_id, a.transaction_sequence_num, a.elapsed_time_seconds, b.program_name
 , b.open_tran, b.STATUS, b.login_time, b.hostname, db_name(b.dbid) dbname
FROM sys.dm_tran_active_snapshot_database_transactions a
JOIN sys.sysprocesses b ON a.session_id = b.spid
ORDER BY elapsed_time_seconds DESC
--查詢生成行版本或可能訪問行版本的活動事務，即涉及使用version_store_reserved_page_count的會話


select db_name(database_id),* from sys.dm_tran_version_store_space_usage order by 3 desc
查詢每個資料庫在tempdb中佔有的消耗的版本儲存空間


SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count,
  SUM(internal_objects_dealloc_page_count*8/1024/1024) AS task_internal_objects_dealloc_size_GB
FROM sys.dm_db_task_space_usage
GROUP BY session_id
order by 4 desc;
--獲取每個會話中當前執行的所有任務的內部物件所消耗的空間

SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  order by 2 desc;
--獲取當前會話中的內部物件在執行和完成任務時所消耗的空間


select name,snapshot_isolation_state_desc from sys.databases order by 2
--查詢資料庫的隔離級別