使用SQL Server动态管理视图的方法

我们可以使用SQL Server动态管理视图确认缺失索引，下面就对这种SQL Server动态管理视图的方法作详细的介绍，供您参考。

由于有了很多新功能，我们可以看到在SQL Server 2005和现在有SQL Server 2008中，Microsoft引进了一些动态管理视图来协助确认基于查询历史的可能索引候选人。

这些动态管理视图是：

n sys.dm_db_missing_index_details –返回关于缺失索引的详细信息。

n sys.dm_db_missing_index_group_stats – 返回缺失索引组的摘要信息

n sys.dm_db_missing_index_groups – 返回一个具体组的缺失索引的信息。

n sys.dm_db_missing_index_columns(index_handle) – 返回在一个索引中缺失的数据库表列的信息。这是一个函数，它要求传递index_handle。

和大多数动态管理视图的跟踪统计数据一样，当SQL Server实例重启，这些数据被完全清除时，这些工作方式基本上是一样的。所以如果你在一个测试环境中工作并且重启你的SQL Server实例，那么这些视图有可能不返回数据。

为了启动，我们将使用一个从SQL Server 2005联机帮助中得到的实例，这要求从AdventureWorks数据库中查询一张表而在StateProvinceID上没有索引，如下所示：

　USE AdventureWorks;  
GO  
SELECT City, StateProvinceID, PostalCode  
FROM Person.Address  
WHERE StateProvinceID = 1;  
GO 

一旦我们运行了上面的查询，数据在动态管理视图中应该可用。让我们来快速看下每一个查询。

第一个查询从sys.dm_db_missing_index_details视图中获取数据。这可能是最有用的一个查询，因为这给我们展示了 object_id、equality_columns 和inequality_columns。另外，我们可以得到关于所含列的其它具体信息。

SELECT * FROM sys.dm_db_missing_index_details 

图一

所以从上面我们执行的查询中，我们可以看到下面的信息：

n equality_columns = “StateProvinceID”，这是因为这个字段和一个相等运算符在WHERE从句中使用。所以SQL Server告诉我们这将是针对索引的很好的选择。

n inequality_columns = “NULL”，如果你使用其它的运算符比如不相等，那么这个字段将会有数据，但是由于我们使用等号，因此没有一个字段将在这里使用。

n included_columns =这是当创建一个索引时使用的其它字段。由于这个查询只使用City、StateProvinceID 和 PostalCode，因此StateProvinceID将在索引中得到处理，当该索引创建时，其它两个字段可能被用作内嵌的字段。

下一个索引从sys.dm_db_missing_index_group_stats中获取数据。这个查询使我们更了解其他统计数据，例如编译，用户查找， 用户扫描等，所以从这里我们可以知道这个查询多久会被访问。如果我们创建一个基于这些信息的新索引，这将帮助我们确定多久使用一个索引可以获得数据。

SELECT * FROM sys.dm_db_missing_index_group_stats 

由于这个查询我们只执行了一次，因此我们的unique_compiles = 1 ，我们的 user_seeks = 1。如果我们再次运行这个查询，我们的user_seeks应该会增加。

图二

下一个视图sys.dm_db_missing_index_groups将会给我们提供index_group_handle 和 index_handle的信息。

SELECT * FROM sys.dm_db_missing_index_groups 

图三

从上面查询得到的结果基本上将用于从sys.dm_db_missing_index_columns函数中获得数据。index_handle值被传递到下一个查询，如下图所示。

SELECT * FROM sys.dm_db_missing_index_columns(1) 

图四

要得到在一个结果集显示的所有数据，下面从SQL Server 2005联机帮助中得到的查询将为我们提供这些数据。

SELECT mig.*, statement AS table_name,  
column_id, column_name, column_usage  
FROM sys.dm_db_missing_index_details AS mid  
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)  
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle  
ORDER BY mig.index_group_handle, mig.index_handle, column_id; 

图五

总结

n 基于这个例子我们可以看到，我们可以在表AdventureWorks.Person.Address的字段StateProvinceID上创建一个新索引，也可以包括columns City和PostalCode。

n 要注意的是当你在一张表中增加或者删除索引时，缺失索引的所有统计数据将在这张表中完全清除。

n 尽管这可能不是完美的，也存在一些局限，但是这至少让我们了解了之前使用SQL Server旧版本时从来不知道的信息。

【编辑推荐】

SQL Server资源锁模式大全

SQL Server查询结果集中的常量

sql server查询平均值的实现

SQL Server FROM子句的语法

SQL Server查询累计值的实现