sql server 临时表(中) Tempdb监控

 一. 监控概述

  tempdb库空间使用的一大特点,是只有一部分对象,例如用户创建的临时表、table变量等,可以用sys.allocation_units和sys.partitions这样的管理视图来管理,许多内部对象和版本存储在这些管理视图中没有体现,所以sp_spaceused的结果和真实的使用情况会有很大差异,tempdb的空间使用是不能用sp_spaceused来跟踪的。使用dbcc showfilestats 可以反映数据文件使用的整体情况,但不能监控到空间被谁以什么样的方式用掉。

  对于tempdb的磁盘空间不足,可能会严重破坏 sql server 生产环境,并会使正在运行的应用程序无法完成操作。可以使用 sys.dm_db_file_space_usage 动态管理视图来监视 tempdb 文件中的这些功能使用的磁盘空间。此外,若要在会话级或任务级监视 tempdb 中的页分配或页释放活动,可以使用动态管理视图 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage。这些视图可用于标识使用 tempdb 中大量磁盘空间的大型查询、临时表或表变量。还可以使用若干个windows性能计数器来监视 tempdb 中的可用空间以及使用 tempdb 的资源。

  (1)使用sys.dm_db_file_space_usage 视图能反映tempdb在几个大类里的空间使用分布, 使用sys.dm_db_session_space_usage 视图能反映tempdb在各会话中空间使用分布。(2)监控磁盘i/o来确认是否有瓶颈。(3)监视tempdb相关的ddl。

二. 监视tempdb空间分布

  2.1 sys.dm_db_file_space_usage视图

    使用该视图可以:返回数据库中每个文件的空间使用信息。下面例出该视图的主要信息,详细视图查看msdn。通过这个视图就能知道tempdb的空间是被哪一块对象使用掉的,是用户对象,还是内部对象,还是版本存储。

列名

数据类型

说明

database_id

smallint

数据库 id

file_id

smallint

文件 id

unallocated_extent_page_count

bigint

文件的未分配区中的总页数。

不包括已分配区中的未使用页

version_store_reserved_page_count

bigint

版本存储分配的统一区中的总页数

user_object_reserved_page_count

bigint

从统一区为数据库中的用户对象分配的总页数

internal_object_reserved_page_count

bigint

从统一区为文件中的内部对象分配的总页数

  2.2 sys.dm_db_session_space_usage视图 

    使用该视图,能返回数据库每个会话分配和释放的页数。此视图仅适用于tempdb数据库。只有在任务结束时才更新计数器; 统计不反映正在运行的任务。

列名

数据类型

描述

session_id

smallint

会话id。

user_objects_alloc_page_count

bigint

此会话为用户对象保留或分配的页数

user_objects_dealloc_page_count

bigint

此会话取消分配且不再为用户对象保留的页数

internal_objects_alloc_page_count

bigint

此会话为内部对象保留或分配的页数

internal_objects_dealloc_page_count

bigint

此会话取消分配且不再为内部对象保留的页数。

user_objects_deferred_dealloc_page_count

bigint

已标记为延迟释放的页数。

  2.3 监视的sql脚本  

    对于监视tempdb三类对象空间分布,这里使用比较”轻量极”对系统影响不大的方法。通过间隔时间运行来监视空间使用,监视包括 dbcc,dmv,dmf 等,把结果输出到一个文件或数据库表里。

-- 间隔每秒执行一次,手工终止或加入作业
use tempdb
while 1=1
begin

     select getdate() as '当前时间'
     
     --------------query1 从文件级查看tempdb使用情况------------------
     /*
       totalextents 是磁盘占用的空间,一个extent为64k
       usedextents  是磁盘使用的空间
     */
     dbcc showfilestats
     
     ------------- query2 查看当前tempdb各对象,占用总空间-------------
     /*
      user_object_reserved_page_count 用户对象包括: 临时表,表变量,表值函数中返回的表,定义的表和索引
      internal_object_reserved_page_count 内部对象包括:排序段,哈希联接,游标
      version_store_reserved_page_count 行版本包括: 触发器,运行联机索引,快照隔离级别或使用行版本控制
      unallocated_extent_page_count:未分配空间(可用空间)
    */
      select  'tempdb' as db, getdate() as  [time],
              sum(user_object_reserved_page_count)*8.0 as  '用户对象占用总空间_kb',
              sum(internal_object_reserved_page_count)*8.0 as '内部对象占用总空间_kb',
              sum(version_store_reserved_page_count) * 8.0 as '行版本占用总空间_kb',
              sum(unallocated_extent_page_count) *8.0 as '未分配总空间_kb'
       from sys.dm_db_file_space_usage
         where database_id=2 
  
     -----------------query3 查询tempdb各会话,占用和释放的空间-------------
     select 
        t1.session_id,
        (t1.internal_objects_alloc_page_count * 8.0) as internal_objects_alloc_kb,
        (t1.user_objects_alloc_page_count * 8.0)as user_objects_alloc_kb,
        (t1.internal_objects_dealloc_page_count* 8.0) as internal_objects_dealloc_kb,
        (t1.user_objects_dealloc_page_count * 8.0)  as user_objects_dealloc_kb
    from sys.dm_db_session_space_usage as t1, 
    sys.dm_exec_sessions as t3 
    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)
    and t1.session_id <>@@spid
    
     -----------------query4 查询tempdb 各会话以及sql语句,占用空间-------------
    /*
     会话的内部对象和用户对象的保留或释放的页数
    */
    select t1.session_id,  
    (t1.internal_objects_alloc_page_count * 8.0) as internal_objects_alloc_kb,
    (t1.user_objects_alloc_page_count * 8.0)as user_objects_alloc_kb,
    (t1.internal_objects_dealloc_page_count* 8.0) as internal_objects_dealloc_kb,
    (t1.user_objects_dealloc_page_count * 8.0)  as user_objects_dealloc_kb,                                                
    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) 
    and t1.session_id <>@@spid

end

  监听如下图所示:

   2.4  行版本监控

      行版本是可以跨会话的,所以在sys.dm_db_session_space_usage中只有内部对象和用户对象所占用的空间查看。如果要监听和回收这部分空间,可使用下面脚本:

--(1)另外查询行版本运行最长的事务
select top 2 
    transaction_id, 
    transaction_sequence_num, 
    elapsed_time_seconds 
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds desc

--(2)根据得到当前会话
select   session_id from sys.dm_tran_session_transactions where transaction_id=@transaction_id

 

三. 监视tempdb的i/o磁盘

  由于tempdb被多个地方广泛使用,有可能造成磁盘的消耗较大,成为i/o瓶颈。除了监视windows性能计数器,还有就是dmv视图。

  3.1 对于windows性能计数器主要包括三个

-- 每次数据传输的平均时间
avg.disk sec/transfer
--磁盘读数据所需的平均时间
avg.disk sec/read
--磁盘写数据所需的平均时间
avg.disk sec/write

  3.2 dmv视图

    下面使用sys.dm_io_virtual_file_stats来确认io瓶颈,它返回数据文件和日志文件的 i/o 统计信息,包括对文件发出的读取/写入次数以及总字节数, file_id字段中1 是数据文件,2是日志文件,脚本如下:

select db_name(database_id) as 'dbname',
file_id,
io_stall_read_ms/num_of_reads as 'avg read ms/transfer',
io_stall_write_ms/num_of_writes as 'avg write ms/transfer'
 from sys.dm_io_virtual_file_stats(null,null)
where  database_id=2

  下面是数据文件及日志文件性能指标建议列表,仅供参考

数据文件 日志文件
目标:<10ms 目标:<5ms
可接受:10 ~ 20 ms 可接受:5~15ms
不可接受:>20ms 不可接受:>15ms

 

四 监视tempdb相关的ddl

  ddl主要是用在定义或改变表的结构,数据类型,表之间的链接和约束等初始化工作上。对于大量,频繁地创建和删除临时表和表变量会引起元数据上的争用。在2008版本中在一定条件下局部临时表和表变量会被缓存,以减少元数据的争用。通过windows性能计数器或sys.dm_os_waiting_tasks 视图可以周期性的检查等待时间较长的会话。

  4.1 sys.dm_os_waiting_tasks 

    使用该视图返回有关正在等待某些资源的任务的等待队列的信息。脚本如下

select session_id,wait_duration_ms,resource_description,getdate()as '当前时间'
from sys.dm_os_waiting_tasks 
where resource_description like '2:%' and wait_type like 'page%latch_%'
order by wait_duration_ms desc

  4.2 windows性能计数器

sql server:access methods\workfiles created/sec
sql server:access methods\worktables created/sec
sql server:access methods\mixed page allocations/sec
sql server:access methods\temp tables created/sec
sql server:access methods\temp tables for destruction

 

  最后还可参考msdn文档:

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐