ITPub博客

首页 > 数据库 > SQL Server > Sqlserver定位哪些对象和哪些会话哪些sql语句消耗了tempdb

Sqlserver定位哪些对象和哪些会话哪些sql语句消耗了tempdb

原创 SQL Server 作者:lusklusklusk 时间:2021-09-23 15:16:11 0 删除 编辑







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
--查询数据库的隔离级别

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2793298/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP, 10年DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    455
  • 访问量
    886229