动态管理视图DMV和函数DMF

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。
拿到系统行为统计信息之后，你终于调整了索引，于是系统明显nb了。如果你要看看它变得有多nb，可以关注动态视图sys.dm_db_index_usage_stats
检测阻塞锁见 [MSSQL锁定-3.死锁与阻塞]

慢慢增加中..........

posted @ 2010-03-10 14:46 xxd 阅读(2207) 评论(2) 编辑收藏举报

刷新页面返回顶部

动态管理视图DMV和函数DMF

公告