SQL Server DBA日常巡检(下)

4、数据文件、日志文件的I/O统计信息

代码如下:

--数据和日志文件的I/O统计信息,包含文件大小
   select database_id,
          file_id,
          file_handle,           --windows文件句柄
          sample_ms,             --自从计算机启动以来的毫秒数
          num_of_reads,
          num_of_bytes_read,
          io_stall_read_ms,      --等待读取的时间
          num_of_writes,
          num_of_bytes_written,
          io_stall_write_ms,
          io_stall,              --用户等待文件完成I/O操作所用的总时间
          size_on_disk_bytes     --文件在磁盘上所占用的实际字节数
   from sys.dm_io_virtual_file_stats(db_id('test'),   --数据库id
                                      1 )  --数据文件id                                       
   union all
   select database_id,
          file_id,
          file_handle,           --windows文件句柄
          sample_ms,             --自从计算机启动以来的毫秒数
          num_of_reads,
          num_of_bytes_read,
          io_stall_read_ms,      --等待读取的时间
          num_of_writes,
          num_of_bytes_written,
          io_stall_write_ms,
          io_stall,              --用户等待文件完成I/O操作所用的总时间
          size_on_disk_bytes     --文件在磁盘上所占用的实际字节数 
   from sys.dm_io_virtual_file_stats( db_id('test'),   --数据库id
                                      2 )  --日志文件id

5、对象,包括:表、索引、索引视图等

代码如下:

--不一定准确:某个表的行数,保留大小,数据大小,索引大小,未使用大小
exec sp_spaceused @objname ='temp_lock'   
--准确:但有性能影响
exec sp_spaceused @objname ='temp_lock',
                 @updateusage ='true'   

--按页统计,没有性能影响,有时不准确  一次计算多个对象的空间使用情况
--sys.dm_db_partition_stats返回当前数据库中每个分区(表和索引)的页和行计----数信息
select o.name,
      sum(p.reserved_page_count) as reserved_page_count, --保留页,包含表和索引
      sum(p.used_page_count) as used_page_count,         --已使用页,包含表和索引
      sum(case when p.index_id <2 
                    then p.in_row_data_page_count + 
                         p.lob_used_page_count + 
                         p.row_overflow_used_page_count
               else p.lob_used_page_count + 
                    p.row_overflow_used_page_count
          end) as data_pages,  --数据页,包含表中数据、索引中的lob数据、索引中的行溢出数据
      sum(case when p.index_id < 2 
                    then p.row_count
               else 0
          end) as row_counts   --数据行数,包含表中的数据行数,不包含索引中的数据条目数
from sys.dm_db_partition_stats p
inner join sys.objects o
       on p.object_id = o.object_id 
where p.object_id= object_id('表名')
group by o.name     

--按页或区统计,有性能影响,准确         
--显示当前数据库中所有的表或视图的数据和索引的空间信息
--包含:逻辑碎片、区碎片(碎片率)、平均页密度               
dbcc showcontig(temp_lock)   

--SQL Server推荐使用的动态性能函数,准确
select *
from sys.dm_db_index_physical_stats(
       db_id('test'),                      --数据库id
       object_id('test.dbo.temp_lock'),    --对象id
       null,                               --索引id
       null,                               --分区号
       'limited'   --default,null,'limited','sampled','detailed',默认为'limited'
                   --'limited'模式运行最快,扫描的页数最少,对于堆会扫描所有页,对于索引只扫描叶级以上的父级页
                   --'sampled'模式会返回堆、索引中所有页的1%样本的统计信息,如果少于1000页,那么用'detailed'代替'sampled'
                   --'detailed'模式会扫描所有页,返回所有统计信息
   )   

--查找哪些对象是需要重建的
use test
go
if OBJECT_ID('extentinfo') is not null
   drop table extentinfo
go
create table extentinfo 
(   [file_id] smallint, 
   page_id int, 
   pg_alloc int,               
   ext_size int,                
   obj_id int,
   index_id int,                
   partition_number int,
   partition_id bigint,
   iam_chain_type varchar(50),  
   pfs_bytes varbinary(10) 

go    

--查询到的盘区信息是数据库的数据文件的盘区信息,日志文件不以盘区为单位

命令格式:  DBCC EXTENTINFO(dbname,tablename,indexid)
DBCC EXTENTINFO('[test]','extentinfo',0)
insert extentinfo 
exec('dbcc extentinfo(''test'') ')
go   
--每一个区有一条数据
select  file_id, 
       obj_id,               --对象ID
       index_id,             --索引id
       page_id,              --这个区是从哪个页开始的,也就是这个区中的第一个页面的页面号
       pg_alloc,             --这个盘区分配的页面数量
       ext_size,             --这个盘区包含了多少页
       partition_number,
       partition_id,
       iam_chain_type,       --IAM链类型:行内数据,行溢出数据,大对象数据
       pfs_bytes 
from extentinfo
order by file_id,
        OBJ_ID,
        index_id,
        partition_id,
        ext_size   

数据库的数据文件的盘区信息,通过计算每个对象理论上区的数量和实际数量,如果两者相差很大,
那就应该重建对象.

1.每一条记录就是一个区
2.如果pg_alloc比ext_size小,也就是实际每个区分配的页数小于理论上这个区的页数,
  那么就会多一条记录,把本应该属于这个区的页放到多出来的这条记录对应的区中,
  那么原来只有一条记录(也就是一个区),现在就有2条记录(也就是2个区),
  导致实际的区数量2大于理论上的区数量1.

select file_id,
      obj_id, 
      index_id, 
      partition_id, 
      ext_size, 
      count(*) as '实际区的个数', 
      sum(pg_alloc) as '实际包含的页数', 
      ceiling(sum(pg_alloc) * 1.0 / ext_size) as '理论上的区的个数', 
      ceiling(sum(pg_alloc) * 1.0 / ext_size) / count(*) * 100.00 as '理论上的区个数 / 实际区的个数' 
from extentinfo 
group by file_id,
        obj_id, 
        index_id,
        partition_id, 
        ext_size 
having ceiling(sum(pg_alloc)*1.0/ext_size) < count(*)   
--过滤: 理论上区的个数 < 实际区的个数,也就是百分比小于100%的
order by partition_id, obj_id, index_id, [file_id]     

6、tempdb数据库

代码如下:

--tempdb数据库的空间使用
tempdb中包含的对象:
用户对象:是用户显式创建的,这些对象位于用户会话的作用域,
        可以位于创建对象的例程(存储过程、触发器、函数)的作用域中。
   1.用户定义的表、索引
   2.系统表、索引
   3.全局临时表、索引
   4.局部临时表、索引
   5.表变量
   6.表值函数中返回的表

内部对象:是根据需要由SQL Server数据库引擎创建的,用于处理SQL Server语句,
         内部对象可以在语句作用域中创建、删除。
         每个内部对象至少需要9个页面,一个IAM页,一个区包含了8个页。
   1.游标、假脱机操作、临时的大型对象(LOB),存储的工作表
   2.哈希联接、哈希聚合操作的工作文件
   3.如果设置了sort_in_tempdb选项,那么创建、重新生成索引的重建排序结果存放在tempdb;
     group by、order by、union操作的中间结果。

版本存储区:是数据页的集合,包含了支持行版本控制功能的所需的数据,主要支持快照事务隔离级别,
           以及一些其他的提高数据库并发性能的新功能。
   1.公用版本存储区:在使用快照隔离级别、已提交读隔离级别的数据库中,由数据修改事务生成的行版本。
   2.联机索引生成版本存储区:为了实现联机索引操作而为数据修改事务生成的行版本,
     多个活动结果集,after触发器生成的行版本。 
                  
上面也提到了,由于sys.allocation_units和sys.partitions视图没有记录tempdb中的内部对象、版本存储区
所以这2个视图和sp_spaceused,不能准确反应出tempdb的空间使用。

分析tempdb现有的工作负载:
   1.设置tempdb的自动增长
   2.通过模拟单独的查询、工作任务,监控tempdb空间使用
   3.通过模拟执行一些系统维护操作(重新生成索引),监控tempdb空间使用
   4.根据2和3中tempdb的空间使用量,预测总工作负荷会使用的空间,并针对任务的并发度调整这个值.
   5.根据4得到的值,设置生成环境中tempdb的初始大小,并开启自动增长.
     另外,tempdb的文件个数和大小,不仅需要满足实际使用需要,还要考虑性能优化.

监控tempdb的空间使用方法:
   1.可以通过SQL Trace来跟踪,但是由于不能预期造成大量使用tempdb语句在什么时候运行,
     而且SQL Trance操作比较昂贵,如果一直开着会产生大量的跟踪文件,对硬盘的负担也比较重,一般不用.

   2.轻量级的监控是通过一定时间间隔运行能够监控系统运行的dbcc命令、动态性能视图-函数,
     把结果记录在文件中,这对于很繁忙的系统是不错的选择。

Select DB_NAME(database_id) as DB, 
      max(FILE_ID) as '文件id',         
      SUM (user_object_reserved_page_count) as '用户对象保留的页数',       ----包含已分配区中的未使用页数
      SUM (internal_object_reserved_page_count) as '内部对象保留的页数',   --包含已分配区中的未使用页数
      SUM (version_store_reserved_page_count)  as '版本存储保留的页数',     
      SUM (unallocated_extent_page_count) as '未分配的区中包含的页数',     --不包含已分配区中的未使用页数   
      SUM(mixed_extent_page_count) as '文件的已分配混合区中:已分配页和未分配页'  --包含IAM页                         
From sys.dm_db_file_space_usage                                          
Where database_id = 2  
group by DB_NAME(database_id)   
                                                
--能够反映当时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,
      t.text
from sys.dm_db_session_space_usage  t1   --反映每个session的累计空间申请                                
inner join sys.dm_exec_sessions as t2 
       on t1.session_id = t2.session_id           
inner join sys.dm_exec_requests t3
       on t2.session_id = t3.session_id                
cross apply sys.dm_exec_sql_text(t3.sql_handle) t
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    

--返回tempdb中页分配和释放活动,
--只有当任务正在运行时,sys.dm_db_task_space_usage才会返回值
--在请求完成时,这些值将按session聚合体现在SYS.dm_db_session_space_usage
select t.session_id,
      t.request_id,
      t.database_id,
      t.user_objects_alloc_page_count,
      t.internal_objects_dealloc_page_count,
      t.internal_objects_alloc_page_count,
      t.internal_objects_dealloc_page_count
from sys.dm_db_task_space_usage t   
inner join sys.dm_exec_sessions e
       on t.session_id = e.session_id        
inner join sys.dm_exec_requests  r    
       on t.session_id = r.session_id and
          t.request_id = r.request_id         

上一篇:实用技巧:使用 jQuery 异步加载 JavaScript 脚本


下一篇:SQL Server DBA日常巡检(上)