tempdb 表空间监控

青旅半醒 2022-06-14 06:12 197阅读 0赞

--监测谁用了SQL Server的Tempdb空间
    select * from sys.dm_db_file_space_usage
    
    tempdb的空间是被哪一块对象使用掉的?
    是用户对象(user_object_reserved_page_count字段)，
    还是系统对象（internal_object_reserved_page_count字段），
    还是版本存储区（version_store_reserved_page_count字段）。
    
    用户对象(user_object_reserved_page_count)
    用户对象由用户显式创建。这些对象可以位于用户会话的作用域中，也可位于创建对象所用例程的作用域中。 可以是存储过程、触发器或用户定义函数。 用户对象可以是下列项之一：
    •用户定义的表和索引•系统表和索引•全局临时表和索引•局部临时表和索引•table 变量•表值函数中返回的表
    
    内部对象（internal_object_reserved_page_count）
    内部对象是根据需要由 SQL Server 数据库引擎创建的，用于处理 SQL Server 语句。 内部对象可以在语句的作用域中创建和删除。 内部对象可以是下列项之一：
    •用于游标。•用于哈希联接或哈希聚合操作的查询。•某些 GROUP BY、ORDER BY 或 UNION 查询的中间排序结果。
    
    版本存储（version_store_reserved_page_count）
    版本存储区主要用来支持Snapshot事务隔离级别，以及SQL 2005以后推出的一些其他提高数据库并发度的新功能。
    
    监视和发现当前的Tempdb使用者:
    总的思路：
    先用“dbcc showfilestats”语句查询当前tempdb的总体使用量。再查询sys.dm_db_file_space_usage视图，得到Tempdb里当前总共有多少用户对象、内部对象、以及版本存储。
    然后查询sys.dm_db_session_space_usage和sys.dm_exec_sessions，找到当前使用Tempdb的所有连接。最后通过sys.dm_exec_sql_text，找到这些连接正在运行的语句。
    
    1、dbcc showfilestats                                                       
    2、
    -- 返回所有做过空间申请的session信息
    Select 'Tempdb' as DB, getdate() as Time,                                                       
        SUM (user_object_reserved_page_count)*8 as user_objects_kb,          
        SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,  
        SUM (version_store_reserved_page_count)*8  as version_store_kb,      
        SUM (unallocated_extent_page_count)*8 as freespace_kb                
    From sys.dm_db_file_space_usage                                          
    Where database_id = 2   
    3、
    -- 这个管理视图能够反映当时tempdb空间的总体分配
    SELECT 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.*
    from sys.dm_db_session_space_usage  t1 ,                                
    -- 反映每个session累计空间申请
    sys.dm_exec_sessions as t3 
    -- 每个session的信息
    where 
    t1.session_id = t3.session_id 
    and (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)
    4、
    -- 返回正在运行并且做过空间申请的session正在运行的语句
    SELECT t1.session_id,                                                    
    st.text                                                         
    from sys.dm_db_session_space_usage as t1,                                
    sys.dm_exec_requests as t4                                               
    CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st                    
     where  t1.session_id = t4.session_id                                        
       and t1.session_id >50                                                 
    and (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)  
    
    ---可以通过增加 数据库内存使用量 或 增加 tempdb库的 数据文件（组）来解决 TEMPDB 的性能