SQL Server: 揭开隐藏数据的面纱，优化应用程序性能

核心提示： Figure 5Identifying Most Costly Unused Indexes此查询结果会显示尚未用于检索数据但已因基础表更改而更新的索引，这些更新显示在 user_updates 列和 system_updates 列中，SQL Server: 揭开隐藏数据的面纱，优化应用

Figure 5Identifying Most Costly Unused Indexes

此查询结果会显示尚未用于检索数据但已因基础表更改而更新的索引。这些更新显示在 user_updates 列和 system_updates 列中。结果按已应用到索引的用户更新数排序。

为确保索引未被使用，您必须要收集足够多的信息，因为您不希望无意删除对可能仅每季度或每年运行一次的查询非常重要的索引。另外，请注意某些索引用于限制插入重复记录或对数据排序；在删除任何未使用的索引前，还必需考虑这些因素。

查询的基本形式仅适用于当前数据库，因为它联接到 sys.indexes DMV，而后者只与当前数据库有关。您可以使用系统存储过程 sp_MSForEachDB 提取服务器上所有数据库的结果。提取模式在侧栏“循环遍历所有数据库”中介绍。在其他要对服务器所有数据库遍历的脚本部分中，我也使用此模式。另外，我过滤掉堆类型的索引，因为这些索引表示没有正式索引的表的本机结构。

高开销的已使用索引

在已使用的索引中确定开销（即对基础表进行的更改）最高的索引很有用。此开销对性能有不良影响，但索引本身可能对数据检索非常重要。

使用 sys.dm_db_index_usage_stats DMV 可以了解使用索引的频率和程度。此 DMV 已联接到 sys.indexes DMV，后者包含创建索引时所使用的详细信息。检查 user_updates 和 system_updates 列将显示维护性最高的索引。图 6 提供了确定最高开销索引所使用的脚本并显示结果。

Figure6Identifying the Most Costly Indexes

-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
　　　　[Maintenance cost]　= (user_updates + system_updates)
　　　　,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
　　　　,DatabaseName = DB_NAME()
　　　　,TableName = OBJECT_NAME(s.[object_id])
　　　　,IndexName = i.name
INTO #TempMaintenanceCost
FROM　 sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON　s.[object_id] = i.[object_id]
　　AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
　　AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
　　AND (user_updates + system_updates) > 0 -- Only report on active rows.
　　AND s.[object_id] = -999　-- Dummy value to get table structure.
;
-- Loop around all the databases on the server.
EXEC sp_MSForEachDB　　'USE [?];
-- Table already exists.
INSERT INTO #TempMaintenanceCost
SELECT TOP 10
　　　　[Maintenance cost]　= (user_updates + system_updates)
　　　　,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
　　　　,DatabaseName = DB_NAME()
　　　　,TableName = OBJECT_NAME(s.[object_id])
　　　　,IndexName = i.name
FROM　 sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON　s.[object_id] = i.[object_id]
　　AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
　　AND i.name IS NOT NULL　　-- Ignore HEAP indexes.
　　AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
　　AND (user_updates + system_updates) > 0 -- Only report on active rows.
ORDER BY [Maintenance cost]　DESC
;
'
-- Select records.
SELECT TOP 10 * FROM #TempMaintenanceCost
ORDER BY [Maintenance cost]　DESC
-- Tidy up.
DROP TABLE #TempMaintenanceCost