有关索引的DMV（转）

转自：http://www.cnblogs.com/CareySon/archive/2012/05/17/2505981.html

1.查看那些被大量更新，却很少被使用的索引

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT                                                    
    DB_NAME() AS DatabaseName 
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName 
    , OBJECT_NAME(s.[object_id]) AS TableName 
    , i.name AS IndexName 
    , s.user_updates 
    , s.system_seeks + s.system_scans + s.system_lookups 
                          AS [System usage] 
INTO #TempUnusedIndexes 
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 
INNER JOIN sys.objects o ON i.object_id = O.object_id    
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?];                           
INSERT INTO #TempUnusedIndexes 
SELECT TOP 20 
    DB_NAME() AS DatabaseName 
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName 
    , OBJECT_NAME(s.[object_id]) AS TableName 
    , i.name AS IndexName 
    , s.user_updates 
    , s.system_seeks + s.system_scans + s.system_lookups 
                                         AS [System usage] 
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 
INNER JOIN sys.objects o ON i.object_id = O.object_id    
WHERE s.database_id = DB_ID() 
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
AND s.user_seeks = 0 
    AND s.user_scans = 0 
    AND s.user_lookups = 0 
AND i.name IS NOT NULL 
ORDER BY s.user_updates DESC'                            
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC 
DROP TABLE #TempUnusedIndexes

结果如图:

这类索引应该被Drop掉 

最高维护代价的索引

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT                                                     
    DB_NAME() AS DatabaseName 
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName 
    , OBJECT_NAME(s.[object_id]) AS TableName 
    , i.name AS IndexName 
    , (s.user_updates ) AS [update usage] 
    , (s.user_seeks + s.user_scans + s.user_lookups) AS [Retrieval usage] 
    , (s.user_updates) - 
      (s.user_seeks + s.user_scans + s.user_lookups) AS [Maintenance cost] 
    , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] 
    , s.last_user_seek 
    , s.last_user_scan 
    , s.last_user_lookup 
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 
INNER JOIN sys.objects o ON i.object_id = O.object_id    
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?];                              
INSERT INTO #TempMaintenanceCost 
SELECT TOP 20 
    DB_NAME() AS DatabaseName 
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName 
    , OBJECT_NAME(s.[object_id]) AS TableName 
    , i.name AS IndexName 
    , (s.user_updates ) AS [update usage] 
    , (s.user_seeks + s.user_scans + s.user_lookups) 
                    AS [Retrieval usage] 
    , (s.user_updates) - 
(s.user_seeks + user_scans + 
                         s.user_lookups) AS [Maintenance cost] 
    , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] 
    , s.last_user_seek 
    , s.last_user_scan 
    , s.last_user_lookup 
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 
INNER JOIN sys.objects o ON i.object_id = O.object_id    
WHERE s.database_id = DB_ID() 
    AND i.name IS NOT NULL 
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
    AND (s.user_seeks + s.user_scans + s.user_lookups) > 0 
ORDER BY [Maintenance cost] DESC'                        
SELECT top 20 * FROM #TempMaintenanceCost ORDER BY [Maintenance cost] DESC 
DROP TABLE #TempMaintenanceCost

结果如图:

Maintenance cost高的应该被Drop掉

使用频繁的索引

--使用频繁的索引 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT                                           
    DB_NAME() AS DatabaseName 
        , SCHEMA_NAME(o.Schema_ID) AS SchemaName 
    , OBJECT_NAME(s.[object_id]) AS TableName 
    , i.name AS IndexName 
    , (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage] 
    , s.user_updates 
    , i.fill_factor 
INTO #TempUsage 
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 
INNER JOIN sys.objects o ON i.object_id = O.object_id    
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?];                               
INSERT INTO #TempUsage 
SELECT TOP 20 
    DB_NAME() AS DatabaseName 
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName 
    , OBJECT_NAME(s.[object_id]) AS TableName 
    , i.name AS IndexName 
    , (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage] 
    , s.user_updates 
    , i.fill_factor 
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 
INNER JOIN sys.objects o ON i.object_id = O.object_id    
WHERE s.database_id = DB_ID() 
    AND i.name IS NOT NULL 
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
ORDER BY [Usage] DESC'                                    
SELECT TOP 20 * FROM #TempUsage ORDER BY [Usage] DESC 
DROP TABLE #TempUsage

结果如图

 这类索引需要格外注意，不要在优化的时候干掉 

碎片最多的索引

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT                                                     
    DB_NAME() AS DatbaseName 
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName 
    , OBJECT_NAME(s.[object_id]) AS TableName 
    , i.name AS IndexName 
    , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 
INTO #TempFragmentation 
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id    
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?];                                
INSERT INTO #TempFragmentation 
SELECT TOP 20 
    DB_NAME() AS DatbaseName 
    , SCHEMA_NAME(o.Schema_ID) AS SchemaName 
    , OBJECT_NAME(s.[object_id]) AS TableName 
    , i.name AS IndexName 
    , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id    
WHERE s.database_id = DB_ID() 
  AND i.name IS NOT NULL 
  AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
ORDER BY [Fragmentation %] DESC'                          
SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC 
DROP TABLE #TempFragmentation

结果如下:

 这类索引需要Rebuild,否则会严重拖累数据库性能

自上次SQL Server重启后，找出完全没有使用的索引

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT                                                 
    DB_NAME() AS DatbaseName 
    , SCHEMA_NAME(O.Schema_ID) AS SchemaName 
    , OBJECT_NAME(I.object_id) AS TableName 
    , I.name AS IndexName 
INTO #TempNeverUsedIndexes 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?];                           
INSERT INTO #TempNeverUsedIndexes 
SELECT 
    DB_NAME() AS DatbaseName 
    , SCHEMA_NAME(O.Schema_ID) AS SchemaName 
    , OBJECT_NAME(I.object_id) AS TableName 
    , I.NAME AS IndexName 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id 
        AND I.index_id = S.index_id 
        AND DATABASE_ID = DB_ID() 
WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0 
  AND I.name IS NOT NULL 
  AND S.object_id IS NULL' 
SELECT * FROM #TempNeverUsedIndexes                         
ORDER BY DatbaseName, SchemaName, TableName, IndexName 
DROP TABLE #TempNeverUsedIndexes

结果如图:

 这类索引应该小心对待，不能一概而论，要看是什么原因导致这种问题

查看索引统计的相关信息

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
    ss.name AS SchemaName 
    , st.name AS TableName 
    , s.name AS IndexName 
    , STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated' 
    , s.rowcnt AS 'Row Count' 
    , s.rowmodctr AS 'Number Of Changes' 
    , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS 
DECIMAL(28,2)) * 100.0) 
                             AS DECIMAL(28,2)) AS '% Rows Changed' 
FROM sys.sysindexes s 
INNER JOIN sys.tables st ON st.[object_id] = s.[id] 
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] 
WHERE s.id > 100 
  AND s.indid > 0 
  AND s.rowcnt >= 500 
ORDER BY SchemaName, TableName, IndexName

结果如下:

 

 因为查询计划是根据统计信息来的，索引的选择同样取决于统计信息，所以根据统计信息更新的多寡可以看出数据库的大体状况，20%的自动更新对于大表来说非常慢。