第七章——DMVs跟DMFs（2）——用DMV和DMF监控索引性能

第七章——DMVs和DMFs（2）——用DMV和DMF监控索引性能

本文继续介绍使用DMO来监控，这次讲述的是监控索引性能。索引是提高查询性能的关键性手段。即使你的表上有合适的索引，你也要时时刻刻进行索引维护任务。

SQLServer有专门的DMO来显示索引相关统计信息。能帮助你分析现有索引的性能情况。通过这些DMO，可以做到：

?  检查索引使用模式

?  查找丢失索引

?  查找无用索引

?  查找索引碎片

?  分析索引页分配明细

本文将使用这些DMO来检查数据库的丢失索引，索引上的查找和扫描操作，并分析索引碎片是否有必要重组或重建。

       下面的例子将演示如何通过DMO来找到丢失索引，丢失索引是不存在但创建后可以提高查询性能的索引。

       同时可以看到如何查找索引使用明细和碎片明细，对索引维护工作很有帮助。

       本文将使用SQLServer 2008R2和示例数据库AdventureWorks来演示。

1、  打开SSMS并连到SQLServer，选择AdventureWorks数据库。

2、  在新窗口中输入以下代码：

USE AdventureWorksGOSELECT  SalesOrderID ,        SalesOrderDetailID ,        OrderQty ,        ProductIDFROM    Sales.SalesOrderDetailWHERE   ModifiedDate >= '2004-01-01 00:00:00.000'GOSELECT  SalesOrderDetailID ,        UnitPrice ,        UnitPriceDiscountFROM    Sales.SalesOrderDetailWHERE   ProductID = 921GO

3、  在另外一个窗口输入以下代码来查找缺失索引的情况：

SELECT  MID.statement AS ObjectName ,        MID.equality_columns ,        MID.inequality_columns ,        MID.included_columns ,        MIGS.avg_user_impact AS ExpectedPerformanceImprovement ,        ( MIGS.user_seeks + MIGS.user_scans ) * MIGS.avg_total_user_cost        * MIGS.avg_user_impact AS PossibleImprovementFROM    sys.dm_db_missing_index_details AS MID        INNER JOIN sys.dm_db_missing_index_groups AS MIG ON MID.index_handle = MIG.index_handle        INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS ON MIG.index_group_handle = MIGS.group_handleGO

4、  为了找到索引使用明细，输入以下代码：

USE AdventureWorksGOSELECT  O.name AS ObjectName ,        I.name AS IndexName ,        IUS.user_seeks ,        IUS.user_scans ,        IUS.last_user_seek ,        IUS.last_user_scanFROM    sys.dm_db_index_usage_stats AS IUS        INNER JOIN sys.indexes AS I ON IUS.object_id = I.object_id                                       AND IUS.index_id = I.index_id        INNER JOIN sys.objects AS O ON IUS.object_id = O.object_idGO

5、  输入以下代码来查找索引碎片：

USE AdventureWorksGOSELECT  O.name AS ObjectName ,        I.name AS IndexName ,        IPS.avg_page_space_used_in_percent AS AverageSpaceUsedInPages ,        IPS.avg_fragmentation_in_percent AS AverageFragmentation ,        IPS.fragment_count AS FragmentCount ,        suggestedIndexOperation = CASE WHEN IPS.avg_fragmentation_in_percent <= 30                                       THEN 'ReOrganize Index'                                       ELSE 'ReBuild Index'                                  ENDFROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')        AS IPS        INNER JOIN sys.indexes AS I ON IPS.index_id = I.index_id                                       AND IPS.object_id = I.object_id        INNER JOIN sys.objects AS O ON IPS.object_id = O.object_idWHERE   IPS.avg_fragmentation_in_percent > 5ORDER BY AverageFragmentation DESC GO

结果如下：

        在本文中，首先在AdventureWorks库上的Sales.SalesOrderDetail表中做一些简单的查询，第一个查询是基于这个表的ModifiedDate，而第二个查询是基于ProductID。

         SQLServer提供缺失索引的详细情况。缺失索引是指在数据库中不存在的索引，但是如果创建，查询将会得益并运行得更快。可以关联两个DMV，sys.dm_db_missing_index_groups和sys.dm_db_missing_index_group_stats。其中DMV，sys.dm_db_missing_index_groups_stats返回关于如果创建了缺失索引，将会对查询有多少可能性的提高的详细信息。注意从这个视图中查找的avg_user_impact列，这里间接地通过这列来和Sys.dm_db_missing_index_details与sys.dm_db_missing_index_groups关联。

和DTA（数据库优化引擎顾问）类似，DMV可能会建议广泛使用很多INCLUDE列在索引中。所以你不应该不顾一切把所有索引全部创建。因为这对你的DML操作可能会带来比较大的影响，如增删改等操作。

在第二个查询DMO中，返回了特定索引的查找和扫描数量。为了返回索引名称和对象名称，关联了sys.indexes和sys.objects这两个目录视图。

       最后就是查找碎片，这里使用了DMF而不是DMV，sys.dm_db_index_physical_stats()函数。留意一下，这里使用了DB_ID()函数，因为希望得到当前数据库的所有索引信息，所以用这个函数即可。为了得到索引名和对象名，关联了sys.indexex和sys.objects目录视图。

除了上面提到的DMO之外，还有两个有用的DMF来协助分析：

?  Sys.dm_db_index_operational_stats

?  Sys.dm_db_missing_index_columns

这两个DMF和之前提到的DMO的详细信息可查看联机丛书或者微软官网的说明。