动态管理视图和动态管理函数学习

动态管理对象是SqlServer2005新增的一个功能，包括动态管理视图和动态管理函数。我大概数了一下里面 提供的动态管理试图大概有70多个，还有10几个动态管理函数(IF)。这个新功能可以对数据库服务器进行高效 监控，为数据库管理员维护数据库提供了非常多的参考资料，相信会越来越受到数据库管理员的喜欢。

查询动态管理视图或函数需要对于对象具有 SELECT 权限以及 VIEW SERVER STATE 或 VIEW DATABASE STATE 权限。这通常也都是管理员才会使用的，所有动态管理视图和函数都存在于 sys 架构中，并遵循 dm_* 命名约定。当使用动态管理视图或函数时，必须使用 sys 架构作为视图或函数名称的前缀。

动态管理视图和函数划分为以下类别。 

与公共语言运行时有关的动态管理视图	与 I/O 有关的动态管理视图和函数
与数据库镜像有关的动态管理视图	与查询通知有关的动态管理视图
与数据库有关的动态管理视图	与复制有关的动态管理视图
与执行有关的动态管理视图和函数	与 Service Broker 有关的动态管理视图
与全文搜索有关的动态管理视图	与 SQL 操作系统有关的动态管理视图
与索引有关的动态管理视图和函数	与事务有关的动态管理视图和函数

既然有这么多的动态管理对象当然也不可能都用到，我这里主要写一点我经常用，因为经常使用感觉很方便 ，给我的工作带来很大帮助，我很希望把我的理解都写下来。

想我高升开始学习SqlServer2005也有2个月了，用这些动态管理对象也有几天了，当然我也是边学边写，很 多都是从MSDN上面copy的。这篇文章是会经常更新的，我会不断学习把我的理解写出来，我这篇文章的地址是http://blog.csdn.net/hb_gx/arch ive/2007/06/05/1639818.aspx 当然我也有很多不懂的地方，也很希望能得到喜欢数据库的热心人士指点。废话不说开始学习。

与索引有关的动态管理视图和函数

1.sys.dm_db_index_usage_stats

动态管理视图 sys.dm_db_index_usage_stats 该视图返回不同类型索引操作的计数以及 上次执行每种操作的时间。由一个查询执行对指定索引所进行的每个单独的搜索、扫描、查找或更新都被计为 对该索引的一次使用，并使此视图中的相应计数器递增。对于由用户提交的查询所引发的操作以及由内部生成 的查询所引发的操作（例如为收集统计信息而进行的扫描），都将报告相应的信息。

user_updates 计数器指示由基础表或视图上的插入、更新或删除操作所引起的索引维护级别。可使用此视 图确定应用程序只是少量使用的索引，或根本未使用的索引。还可以使用此视图确定引发维护开销的索引。您 可能要删除引发维护开销但不用于查询或只是偶尔用于查询的索引。

只要启动 SQL Server (MSSQLSERVER) 服务，计数器就初始化为空。而且，当分离或关闭数据库时（例如， 由于 AUTO_CLOSE 设置为 ON），便会删除与该数据库关联的所有行。 使用索引时，如果某行并未针对该索引 而存在，则将该行添加到 sys.dm_db_index_usage_stats 中。当添加该行时，它的计数器会初始设置为零。

这个视图最关键的就是最关键的就是这4个字段：

• user_seeks 通过用户查询执行的搜索次数,就是利用聚集索引的次数。

• user_scans 通过用户查询执行的扫描次数，就是没有利用任何索引的次数，逐行扫描最慢的那种。

• user_lookups 通过用户查询执行的查找次数，就是利用非聚集索引的次数。

• user_updates 通过用户查询执行的更新次数，就是更新前找到这条数据时利用的索引的次数 。因为更新是先查询到需要更新的数据，然后执行更新命令所以更新的时候这个user_updates字段和 user_seeks、user_lookups、user_scans字段中的一个都会加一。

我所理解的就是一个用户的每一次查询都会在该试图上留下记录，下面针对MS提供的 AdventureWorks 数据库的 Employee 表进行说明：

select   *   from  HumanResources.Employee

执行这条语句没有用上任何索引，只是针对Employee表做了一个全盘扫描，查询出了所有 的数据，所以在试图上 PK_Employee_EmployeeID 这个索引的 user_scans 字段会加1

select count(*) from HumanResources.Employee

执行这条语句的时候编辑器会利用一个最适合的索引来扫描，具体利用的那一个看看查询 计划就知道(Ctrl+L)，我这里看到的是 IX_Employee_ManagerID 索引的 user_scans 字段加1。这是个很聪明 的选择，扫描索引页来统计要比扫描数据页要来的更快一些了。曾经听人说count('某字段')比count(*)来的更 有效率，那么我想在sql2005里面这句话应该就没道理了，因为这两个的执行计划是一样的。也不知道我的理解 对不对？

select * from HumanResources.Employee where EmployeeID = 25

执行这条语句利用了聚集索引，所以 user_seeks 字段加1

那么这个视图提供了这些信息对我们有什么用呢？我想最大的作用就是能够让我们知道我 们建的索引的使用情况。

如果一个索引长期没有怎么使用那么这个索引也就失去了它存在的意义，删掉并释放它所 占用的索引页吧。

如果一个索引的更新太多也不是什么好事情，这意味着每次更新的时候都可能重整索引页 ，这样反而影响了性能。更新一个值本来很快，可是因为索引页的调整浪费了时间，当然如果这里的填充因子 设置的比较合理的话这个问题也就得到解决了。

如果扫描的次数比利用索引的次数多，那么就要考虑了是换字段建索引还是改查询语句， 让查询语句充分利用到索引。

当然还有很多值得我们参考的东西，我暂时只想到这么多，水平有限以后会修改的，希望 能得到各位的指点。

这个动态管理里的数据都是从数据库的事务日志中来，看了一个创建这个视图的sql语句 很可惜我这样子写老是报错，不明白这样是怎样打开日志文件的 “OpenRowSet(TABLE LOGINDEXSTATS) ”？？？这个OpenRowSet我是不用的，都是用的LinkServer。

2.sys.dm_db_index_physical_stats

返回指定表或视图的数据和索引的大小和碎片信息。以前都是使用DBCC SHOWCONTIG 语句 查看碎片信息，但是DBCC SHOWCONTIG返回的结果不好放到结果集中，现在提供的这个函数反映的信息更多了， 也更方便我们分析。

函数的5个参数：

• database_id 数据库的ID。可以为null，表示所有的数据库，这个通常还是要指定，因 为不指定可是会检查所有的数据库，要花多少时间啊。

• object_id 该索引所基于的表或视图的对象 ID。可以为null，如果前一个参数的值 为null则这个参数必须为null。

• index_id 索引的 ID。表或者视图中的某个索引的ID，可以为null，如果前一 个参数的值为null则这个参数必须为null。

• partition_number 对象中的分区号。这个参数是针对分区表的。

• mode 模式名称。mode 指定用于获取统计信息的扫描级别。有效输入 为 DEFAULT、NULL、LIMITED、SAMPLED或DETAILED。默认值 (NULL) 为 LIMITED。如果想得到更详细的信息可 以使用SAMPLED或DETAILED，当然这样检测的速度也越慢。

 我认为这个函数返回的字段最重要的就是这4个了

• avg_fragmentation_in_percent 索引的逻辑碎片。为了获得最佳性能，这个值应尽可能接近零。但是，从 0 到 10％ 范围内的值都可以接受。太大了就准备整理索引吧。

• fragment_count 碎片数。

• avg_fragment_size_in_pages 碎片的平均页数。 这个值和avg_fragmentation_in_percent 成反比，越大范围扫描的性能越好。

• page_count 索引或数据页的总数。

我整理索引通常都是依据这两个数来决定。最近公司又上了新的项目，数据库也增加了，完全靠维护计划任务里面的重新组织和重新生成索引需要太多的时间，而且有时候会和数据库的备份发生冲突，导致这个任务经常失败。现在重整索引都是执行这个动态管理函数搜索出需要整理的索引个别的进行整理。

我这里还是以 Employee 表进行说明，扫描模式用的SAMPLED，并且查询出索引名：

USE  AdventureWorks
GO

SELECT  b.name,a. *   FROM  sys.dm_db_index_physical_stats

( DB_ID ( ' AdventureWorks ' ), OBJECT_ID ( ' HumanResources.Employee ' ), null , null , 'SAMPLED') a
left   join  sys.indexes b  ON  a. object_id   =  b. object_id   and  a.index_id  =  b.index_id
GO

注意：如果是 SQL Server 8.0或以下版本的数据库，在这里需要声明两个变量，如：

DECLARE @db_id int
DECLARE @object_id int
SET @db_id = DB_ID('AdventureWorks')
SET @object_id = OBJECT_ID('HumanResources.Employee')

SELECT b.name,a.* FROM sys.dm_db_index_physical_stats(@db_id,@object_id,null,null,'SAMPLED') a
left join sys.indexes b ON a.object_id = b.object_id and a.index_id = b.index_id
GO

其实我的例子写的不好没有做任何的判断，SQL Server 2005 联机丛书上有完整的例子，比我这个好多了。

3.sys.dm_db_index_operational_stats

返回数据库中表或索引的每个分区的当前低级 I/O、锁定、闩锁和访问方法活动。这个函数和上一个差不多，只是提供的信息更详细。由于我没用过不熟悉，应该属于不常用的函数，也不多说了。

使用 sys.dm_db_index_usage_stats 视图和 sys.dm_db_index_physical_stats 函数对我维护数据库的索引提供了极大的帮助。这两个动态管理对象结合使用能够很好的判断出索引的使用情况，对索引的优化提供了有利的依据，今后有新的心得会继续补上。

我这篇文章的地址：http://blog.csdn.net/hb_gx/arch ive/2007/06/05/1639818.aspx，本人高升由于公司缺人临时扮演了DBA的角色，希望能得到大家的帮助，谢谢！