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

时间：2022-07-23 15:50:15

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

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

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

Ø  检查索引使用模式

Ø  查找丢失索引

Ø  查找无用索引

Ø  查找索引碎片

Ø  分析索引页分配明细

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

准备工作：

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

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

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

步骤：

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

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

USE AdventureWorks

GO

SELECT  SalesOrderID ,

        SalesOrderDetailID ,

        OrderQty ,

        ProductID

FROM    Sales.SalesOrderDetail

WHERE   ModifiedDate >= '2004-01-01 00:00:00.000'

GO

SELECT  SalesOrderDetailID ,

        UnitPrice ,

        UnitPriceDiscount

FROM    Sales.SalesOrderDetail

WHERE   ProductID = 921

GO

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 PossibleImprovement

FROM    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_handle

GO

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

USE AdventureWorks

GO

SELECT  O.name AS ObjectName ,

        I.name AS IndexName ,

        IUS.user_seeks ,

        IUS.user_scans ,

        IUS.last_user_seek ,

        IUS.last_user_scan

FROM    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_id

GO

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

USE AdventureWorks

GO

SELECT  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'

                                  END

FROM    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_id

WHERE   IPS.avg_fragmentation_in_percent > 5

ORDER BY AverageFragmentation DESC

GO

结果如下：

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

分析：

在本文中，首先在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的详细信息可查看联机丛书或者微软官网的说明。

相关文章