动态管理视图DMV和函数DMF

最新推荐文章于 2024-11-01 12:20:35 发布

一直学习

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量1k

点赞数

分类专栏： MS-SQL 文章标签： statistics 数据库 sql server object io sql

MS-SQL 专栏收录该内容

62 篇文章 0 订阅

订阅专栏

Reference

http://gallery.technet.microsoft.com/ScriptCenter/en-us/

http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx

简介

SQL Server 05提供了动态管理视图Dynamic Management Views和函数 Functions,方便了我们对系统运行情况的监控,故障诊断和性能优化.配合Profiler,dashboard一起使用很不错.

常规服务器动态管理对象包括：

dm_db_*：数据库和数据库对象
dm_exec_*：执行用户代码和关联的连接
- --dm_exec_session:类似2k时的sysprocesses 返回:资源,账号等......
- --dm_exec_connections:返回用户认证方式和IP
- --dm_exec_query_stats:
dm_os_*：内存、锁定和时间安排
- --dm_os_buffer_descriptors
dm_tran_*：事务和隔离
- --dm_tran_locks: 查看锁定
dm_io_*：网络和磁盘的输入/输出

我的脚本(DBA 下的存储过程)

myScript.spBufferUsed 4.2查询某个数据库内个对象使用内存缓存区资源的统计
myScript.spHighestCPUTime 4.5呈现累积最耗CPU时间的前50个执行计划
myScript.spListRecompile 4.7 显示累计最常重新编译的25个运行计划
myScript.spReusedPlans 4.9 重用执行计划
myScript.spBlockInfo 4.10 显示锁定与被锁定之间的链状关系
- dm_tran_locks没返回一条记录都代表一个已经授予或等待授予的锁定,主要分为:资源"resource"和要求"request"两类前缀
- dm_os_waiting_tasks提供等待时间统计,谁锁定谁的链接信息
- dm_exec_requests和dm_exec_sql_text返回T-SQL
myScript.spIOInfo 4.13 监控是否有IO延迟的状况 & spIOTopWastedInfo 4.14最耗IO资源的SQL语句
- dm_io_pending_io_requests每有一行返回代表有一项暂停的IO请求
- dm_io_virtual_file_stats动态管理函数可返回数据库文件和记录文件的IO使用统计数据
myScript.spTempdbUsageStatistics 4.15 统计各连接对 tempdb 系统数据库的占用量
myScript.spCurrentTempdbUsageStatement 4.16 显示目前连接的语法，及其对 tempdb 系统数据库累计的使用量

常用的SQL Server监控手段总结

A、查看SQL语句的执行计划，可以在查询分析其使用CTRL+L图形化的显示执行计划，一般应该注意百分比最大的几个图形的属性，把鼠标移动到其上面会显示这个图形的属性，
需要注意预计成本的数据，也要注意其标题，一般都是CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等，其中出现SCAN说明语句有优化的余地。SET SHOWPLAN_ALL ON; SET STATISTICS IO on; SET STATISTICS TIME on; 查看执行计划的文本详细信息。
B、用事件探查器跟踪系统的运行，可疑跟踪到执行的语句，以及所用的时间，CPU用量以及I/O数据，从而分析语句的效率。
C、可以用WINDOWS的系统性能检测器，关注CPU、I/O参数

使用sys.dm_exec_query_stats和sys.dm_exec_sql_text找到CPU占用率高的语句
各种SQL的执行次数、逻辑IO、物理IO、执行消耗CPU时间等等等等。想想看，假如你拿了一份系统中所有SQL的文本、执行总次数、逻辑IO占用总IO比例、物理IO占用总IO比例、平均逻辑IO、平均物理IO等等等等，你八成能够指出系统瓶颈所在，老板和伙计们的眼光也会会极大的满足你小小的虚荣心，哈。这些东西就在动态视图sys.dm_exec_query_stats里面，自个翻翻联机文档吧：）这里有篇文章不错

     
     
      
      SELECT
      
       
      
      TOP
      
       
      
      100
      
       execution_count,
           total_logical_reads 
      
      /
      
      execution_count 
      
      AS
      
       
      
      [
      
      Avg Logical Reads
      
      ]
      
      ,
           total_elapsed_time 
      
      /
      
      execution_count 
      
      AS
      
       
      
      [
      
      Avg Elapsed Time
      
      ]
      
      ,
                
      
      db_name
      
      (st.dbid) 
      
      as
      
       
      
      [
      
      database name
      
      ]
      
      ,
           
      
      object_name
      
      (st.dbid) 
      
      as
      
       
      
      [
      
      object name
      
      ]
      
      ,
           
      
      object_name
      
      (st.objectid) 
      
      as
      
       
      
      [
      
      object name 1
      
      ]
      
      ,
           
      
      SUBSTRING
      
      (st.
      
      text
      
      , (qs.statement_start_offset 
      
      /
      
       
      
      2
      
      ) 
      
      +
      
       
      
      1
      
      , 
           ((
      
      CASE
      
       statement_end_offset 
      
      WHEN
      
       
      
      -
      
       
      
      1
      
       
      
      THEN
      
       
      
      DATALENGTH
      
      (st.
      
      text
      
      ) 

      
                   ELSE
      
       qs.statement_end_offset 
      
      END
      
       
      
      -
      
       qs.statement_start_offset) 
             
      
      /
      
       
      
      2
      
      ) 
      
      +
      
       
      
      1
      
      ) 
      
      AS
      
       statement_text
  
      
      FROM
      
       sys.dm_exec_query_stats 
      
      AS
      
       qs 
      
      
 CROSS
      
       APPLY sys.dm_exec_sql_text(qs.sql_handle) 
      
      AS
      
       st
 
      
      WHERE
      
       execution_count 
      
      >
      
       
      
      100
      
       
      
      
   and
      
       
      
      db_name
      
      (st.dbid) 
      
      is
      
       
      
      not
      
       
      
      null
      
       
      
      and
      
       
      
      db_name
      
      (st.dbid) 
      
      not
      
       
      
      in
      
       (
      
      '
      
      distribution
      
      '
      
      )
 
      
      ORDER
      
       
      
      BY
      
       
      
      1
      
       
      
      DESC
      
      ;
      
       --关于statement_start_offset/2的疑问 
      
      

     
     
     
     
      
      --注意dm_exec_query_stats仅仅统计与分析在高速缓存中的执行计划,并非所有SQL Server
     
     
     
     
      
      耗时的工作都会有执行计划高速缓存,例如DBCC DBReindex不会被缓存

IO跟踪

     
     
      
      set
      
       
      
      statistics
      
       io 
      
      on
      
      

      
      go
      
      

      
      select
      
       
      
      top
      
       
      
      1
      
       
      
      *
      
       
      
      from
      
       sales.customer 
      
      where
      
       customertype 
      
      <>
      
       
      
      '
      
      S
      
      '
      
      ;

      
      
      
      
       
       CustomerID  TerritoryID AccountNumber CustomerType rowguid                              ModifiedDate
      
      
      
      CustomerID  TerritoryID AccountNumber CustomerType rowguid                              ModifiedDate
----------- ----------- ------------- ------------ ------------------------------------ -----------------------
11000       9           AW00011000    I            477586B3-2977-4E54-B1A8-569AB2C7C4D4 2004-10-13 11:15:07.263
(1 行受影响)
表 'Customer'。扫描计数 1，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

      
      --如果需要清理缓存池 
      
      DBCC DROPCLEANBUFFER

CPU时间

     
     
      
      declare
      
       
      
      @x
      
       
      
      int
      
      ;

      
      declare
      
       
      
      @cpu_start
      
       
      
      int
      
      ;

      
      set
      
       
      
      @x
      
       
      
      =
      
       
      
      1
      
      ;

      
      set
      
       
      
      @cpu_start
      
       
      
      =
      
       
      
      @@cpu_busy
      
      ;

      
      while
      
       
      
      @x
      
       
      
      <
      
       
      
      10000
      
      
    
      
      set
      
       
      
      @x
      
       
      
      =
      
       
      
      @x
      
       
      
      +
      
       
      
      1
      
      ;

      
      print
      
       
      
      '
      
      ms of cput for loop1:
      
      '
      
       

      
                +
      
       
      
      cast
      
       ( (
      
      @@cpu_busy
      
       
      
      -
      
       
      
      @cpu_start
      
      ) 
      
      +
      
       
      
      @@timeticks
      
       
      
      /
      
       
      
      1000
      
       
      
      as
      
       
      
      char
      
      );

      
      set
      
       
      
      @cpu_start
      
       
      
      =
      
       
      
      @@cpu_busy
      
      ;

      
      while
      
       
      
      @x
      
       
      
      <
      
       
      
      100000
      
      
    
      
      set
      
       
      
      @x
      
       
      
      =
      
       
      
      @x
      
       
      
      +
      
       
      
      1
      
      ;

      
      print
      
       
      
      '
      
      ms of cput for loop1:
      
      '
      
       
     
     
     
     
      
       
      
                +
      
       
      
      cast
      
       ( (
      
      @@cpu_busy
      
       
      
      -
      
       
      
      @cpu_start
      
      ) 
      
      +
      
       
      
      @@timeticks
      
       
      
      /
      
       
      
      1000
      
       
      
      as
      
       
      
      char
      
      );
     
     
     
     
      
      --注意这两个参数 @@cpu_busy @@timeticks

使用sys.dm_exec_cached_plans和sys.dm_exec_sql_text找到执行最频繁的语句这里有篇文章不错
当dbid值是32767时，就会出现这种情况。因为数据库的ID号与系统数据库，即所谓的资源库是有联系的。这个资源库不是众所周知，但它却是存在于系统中的一个实际数据库，他的确存在，但你在SQL Server Management Studio中却看不到它。在你的数据文件目录下，有一个以字符串“mssqlsystemreource”开始命名的MDF和LDF文件，那就是资源数据库了

     
     
      
      SELECT
      
       
      
      CASE
      
       
      
      when
      
       dbid 
      
      =
      
       
      
      32767
      
       
            
      
      then
      
       
      
      '
      
      Resource
      
      '
      
       
            
      
      else
      
       
      
      DB_NAME
      
      (dbid) 
      
      end
      
       
      
      [
      
      DB_NAME
      
      ]
      
      , 
       OBJECT_SCHEMA_NAME(objectid,dbid) 
      
      AS
      
       
      
      [
      
      SCHEMA_NAME
      
      ]
      
      , 
       
      
      OBJECT_NAME
      
      (objectid,dbid)
      
      AS
      
       
      
      [
      
      OBJECT_NAME
      
      ]
      
      , 
       
      
      SUM
      
      (usecounts) 
      
      AS
      
       
      
      [
      
      Use_Count
      
      ]
      
      ,        
       
      
      SUM
      
      (total_elapsed_time) 
      
      AS
      
       
      
      [
      
      total_elapsed_time
      
      ]
      
      ,
       
      
      SUM
      
      (total_elapsed_time) 
      
      /
      
       
      
      SUM
      
      (usecounts) 
      
      *
      
       
      
      1.0
      
       
      
      AS
      
       
      
      [
      
      avg_elapsed_time
      
      ]
      
      ,
       
      
      substring
      
      (
      
      convert
      
      (
      
      char
      
      (
      
      23
      
      ),
      
      DATEADD
      
      (ms,
      
      sum
      
      (total_elapsed_time)
      
      /
      
      1000
      
      ,
      
      0
      
      ),
      
      121
      
      ),
      
      12
      
      ,
      
      23
      
      ) 
     
     
     
     
      
             AS total_elapsed_time_ms,
     
     
     
     
      
             dbid, 
       objectid  

      
      FROM
      
       sys.dm_exec_cached_plans cp
   
      
      CROSS
      
       APPLY sys.dm_exec_sql_text(cp.plan_handle)  
   
      
      JOIN
      
       
   (
      
      SELECT
      
       
      
      SUM
      
      (total_elapsed_time) 
      
      AS
      
       
      
      [
      
      total_elapsed_time
      
      ]
      
      ,
           plan_handle  
      
      
      FROM
      
       sys.dm_exec_query_stats  
      
      
      GROUP
      
       
      
      BY
      
       plan_handle) qs
    
      
      ON
      
       cp.plan_handle 
      
      =
      
       qs.plan_handle 

      
      WHERE
      
       objtype 
      
      =
      
       
      
      '
      
      Proc
      
      '
      
      
  
      
      AND
      
       
      
      UPPER
      
      (

      
      --
      
       remove white space first
      
      

      
                  
      
      REPLACE
      
      ( 
             
      
      REPLACE
      
      (
              
      
      REPLACE
      
      (
               
      
      REPLACE
      
      (
                
      
      REPLACE
      
      (
                 
      
      REPLACE
      
      (
                  
      
      REPLACE
      
      (
      
      text
      
      ,
      
      '
      
             
      
      '
      
      ,
      
      '
      
       
      
      '
      
      ),
                 
      
      '
      
             
      
      '
      
      ,
      
      '
      
       
      
      '
      
      ),
                
      
      '
      
            
      
      '
      
      ,
      
      '
      
       
      
      '
      
      ),
               
      
      '
      
           
      
      '
      
      , 
      
      '
      
       
      
      '
      
      ),
              
      
      '
      
          
      
      '
      
      ,
      
      '
      
       
      
      '
      
      ),
             
      
      '
      
         
      
      '
      
      ,
      
      '
      
       
      
      '
      
      ),
            
      
      '
      
        
      
      '
      
      ,
      
      '
      
       
      
      '
      
      )
            ) 
       
      
      LIKE
      
       
      
      '
      
      %CREATE PROC%
      
      '
      
      

      
      GROUP
      
       
      
      BY
      
       dbid, objectid

      
      ORDER
      
       
      
      BY
      
       
      
      SUM
      
      (total_elapsed_time) 
      
      /
      
       
      
      SUM
      
      (usecounts) 
      
      *
      
       
      
      1.0
      
       
      
      DESC
      
      ;

sys.dm_db_index_physical_stats
DBCC SHOWCONTIG was replaced by sys.dm_db_index_physical_stats. Under the covers though, they both use the same code - and the I/O characteristics haven't changed.
一些避免碎片的建议

创建数据库时，尽量为数据文件分配一个大的空间。你可以估计某一时期（比如至少3年）可能达到的最大值。
有时将数据文件设为自动增长是可行的。通过设置数据文件最大增长尺寸保持增量限制，在硬盘上保留一些可用空间。
一段时间后，如果需要，确定并重新估计数据库尺寸的最大值，为数据库添加更多的文件或者文件组。
如果有很多数据文件共用一个磁盘分区，别让数据文件自动增长。如果数据文件使用过多，那么将它们分配到不同的文件组或者磁盘。
定期执行数据库维护任务，如DBCC DBREINDEX、重新编译存储过程和触发器。
如果表的记录被频繁的修改或者删除，最好在该表上不定期的运行UPDATE STATISTICS，这将从执行计划里帮助避免性能降低。

 
           SELECT 
            object_name(i.object_id)  
           as 
            object_name,i. 
           name 
            as 
           IndexName,ps.avg_fragmentation_in_percent,avg_page_space_used_in_percent 
          
           FROM 
           sys.dm_db_index_physical_stats (db_id(),  
           NULL 
           ,  
           NULL 
           ,  
           NULL 
           ,  
           'DETAILED' 
           )  
           as 
           ps 
          
           INNER 
           JOIN 
           sys.indexes  
           as 
           i 
          
           ON 
           i.object_id = ps.object_id  
           AND 
           i.index_id = ps.index_id 
          
           WHERE 
           ps.avg_fragmentation_in_percent > 50  
           AND 
           ps.index_id > 0 
          
           ORDER 
           BY 
           1 
          
           object_name IndexName                              avg_fragmentation_in_percent avg_page_space_used_in_percent 
          
           ----------- --------------------------------- ---------------------------- ------------------------------ 
          
           Employee    PK_Employee_EmployeeID              85.7142857142857             97.8679145045713 
          
           Employee    AK_Employee_LoginID                   66.6666666666667             66.5843093649617 
          
           Individual  PK_Individual_CustomerID                87.5                         61.851371386212 
          
           Individual  XMLVALUE_Individual_Demographics  100                          60.5339263652088

 
           SELECT 
            *  
           FROM 
           sys.dm_db_index_physical_stats (DB_ID(N 
           'AdventureWorks' 
           ), OBJECT_ID(N 
           'dbo.DatabaseLog' 
           ),  
           NULL 
           ,  
           NULL 
           ,  
           'DETAILED' 
           ); 
          
 
           --index_type_desc: HEAP,NON or CLUSTERED INDEX 
          
 
           --alloc_unit_type_desc: IN_ROW_DATA,LOB_DATA 
          
 
           --about index: index_depth, index_level 
          
 
           --about fragment: avg_fragmentation_in_percent,avg_fragment_size_in_pages 
          

IN_ROW_DATA包含除大型对象 (LOB) 数据以外的所有数据的数据行或索引行。页的类型为 Data 或 Index。
LOB_DATA 以下列一种或多种数据类型存储的大型对象数据：text、ntext、image、xml、varchar(max)、nvarchar(max)、varbinary(max) 或 CLR 用户定义类型 (CLR UDT)。页的类型为 Text/Image。
ROW_OVERFLOW_DATA 存储在超过 8,060 字节行大小限制的 varchar、nvarchar、varbinary 或 sql_variant 列中的可变长度数据。页的类型为 Text/Image。