在SQL Server中，索引是優化SQL性能的一大法寶。但是由於各種原因，索引會被當做“銀彈”濫用，一方面有些開發人員（甚至是部分資料庫管理員）有一些陋習，不管三七二十一，總是根據所謂的"感覺"或“經驗”先增加一些索引，而不管這些索引是否未被使用或是否合理。另外一方面在資料庫的生命周期中，需求總是... ...
在SQL Server中，索引是優化SQL性能的一大法寶。但是由於各種原因，索引會被當做“銀彈”濫用，一方面有些開發人員（甚至是部分資料庫管理員）有一些陋習，不管三七二十一，總是根據所謂的"感覺"或“經驗”先增加一些索引，而不管這些索引是否未被使用或是否合理。另外一方面在資料庫的生命周期中，需求總是在變化，業務也在變化，有些當初創建的有效索引可能已經變成了unused index了。變成了資料庫性能的累贅； 另外，部分資料庫管理員其實很少清理索引（冗餘索引，重覆索引，未使用索引）。其實不管是出於性能考慮，還是資料庫維護管理的需要，資料庫中的未使用索引（unused index）都需要定期清理，因為這些未使用索引(unused index)不但不會提高查詢性能，還會影響DML操作的性能、浪費存儲空間等等。本文主要總結一下，如何找到識別、查找哪些未使用的索引（unused index)
如何找到未使用索引呢？ 在ORACLE資料庫中提供了監控索引使用情況的功能。雖然在SQL Server中沒有提供此類功能，但是提供了DMV視圖sys.dm_db_index_usage_stats ，關於這個視圖，詳細信息可以參考官方文檔，下麵僅僅介紹需要用到的幾個欄位
user_scans 用戶查詢執行的掃描次數。
user_seeks 用戶查詢執行的搜索次數。
user_lookups 用戶查詢執行的書簽查找次數。
user_updates 通過用戶查詢執行的更新次數。這表示插入、 刪除，更新的次數，而不是受影響的實際行數。
例如，如果你刪除在一個語句中的 1000行，此計數遞增 1
Number of updates by user queries. This includes Insert, Delete, and Updates representing
number of operations done not the actual rows affected. For example, if you delete 1000
rows in one statement, this count increments by 1
我們可以使用下麵SQL語句查找當前資料庫中的未使用索引(unused index)：
SELECT 'SQL Server Instance Start with ' + CONVERT(VARCHAR(16),create_date,120) FROM sys.databases
WHERE database_id =2;SELECT DB_NAME(diu.database_id) AS DatabaseName ,
s.name +'.' +QUOTENAME(o.name) AS TableName ,
i.index_id AS IndexID , i.name AS IndexName ,CASE WHEN i.is_unique =1 THEN 'UNIQUE INDEX'
ELSE 'NOT UNIQUE INDEX' END AS IS_UNIQUE,
CASE WHEN i.is_disabled=1 THEN 'DISABLE'
ELSE 'ENABLE' END AS IndexStatus,
o.create_date AS IndexCreated, STATS_DATE(o.object_id,i.index_id) AS StatisticsUpdateDate, diu.user_seeks AS UserSeek , diu.user_scans AS UserScans , diu.user_lookups AS UserLookups , diu.user_updates AS UserUpdates ,p.TableRows ,
'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(diu.object_id)) +';' AS 'Drop Index Statement'
FROM sys.dm_db_index_usage_stats diuINNER JOIN sys.indexes i ON i.index_id = diu.index_id
AND diu.object_id = i.object_idINNER JOIN sys.objects o ON diu.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN ( SELECT SUM(p.rows) TableRows ,
p.index_id ,
p.object_id
FROM sys.partitions pGROUP BY p.index_id ,
p.object_id
) p ON p.index_id = diu.index_id AND diu.object_id = p.object_idWHERE OBJECTPROPERTY(diu.object_id, 'IsUserTable') = 1
AND diu.database_id = DB_ID() AND i.is_primary_key = 0 --排除主鍵索引 AND i.is_unique_constraint = 0 --排除唯一索引 AND diu.user_updates <> 0 --排除沒有數據變化的索引 AND diu.user_lookups = 0 AND diu.user_seeks = 0 AND diu.user_scans = 0AND i.name IS NOT NULL --排除那些沒有任何索引的堆表
ORDER BY ( diu.user_seeks + diu.user_scans + diu.user_lookups ) ASC,diu.user_updates DESC;
GO
需要註意的幾點：
1：sys.dm_db_index_usage_stats返回索引的被使用的信息，但是這個DMV視圖中的數據是自資料庫服務啟動以來累計收集的數據（只要重啟SQL Server服務，該視圖的計數器就初始化為空。 而且，當分離或關閉資料庫時（例如，由於 AUTO_CLOSE 設置為 ON），便會刪除與該資料庫關聯的所有記錄。），所以，如果資料庫只運行了幾天，那麼這個視圖的數據有可能不是特別準確（例如，有些OLAP的批處理或作業，一個月才運行一次）。所以在判斷分析前，一定要查看資料庫服務已經運行多長時間了。一般合適的時間是一個月以上，最好是兩個月以上。
2：sys.dm_db_index_usage_stats不返回有關記憶體列存儲索引的信息
3：註意欄位IndexCreated，如果索引是最近幾天創建的，也要謹慎分析，不要急於刪除。
4：註意條件裡面有些欄位過濾條件，其實都是包含一定業務意義的。
另外，上面腳本只能查詢當前資料庫的未使用索引，如果需要查詢當前實例下的所有資料庫，那麼可以使用下麵腳本
EXEC sp_MSforeachdb 'USE [?] ;
SELECT DB_NAME(diu.database_id) AS DatabaseName ,
s.name +''.'' +QUOTENAME(o.name) AS TableName ,
i.index_id AS IndexID ,
i.name AS IndexName ,
CASE WHEN i.is_unique =1 THEN ''UNIQUE INDEX''
ELSE ''NOT UNIQUE INDEX'' END AS IS_UNIQUE,
CASE WHEN i.is_disabled=1 THEN ''DISABLE''
ELSE ''ENABLE'' END AS IndexStatus,
o.create_date AS IndexCreated,
STATS_DATE(o.object_id,i.index_id) AS StatisticsUpdateDate,
diu.user_seeks AS UserSeek ,
diu.user_scans AS UserScans ,
diu.user_lookups AS UserLookups ,
diu.user_updates AS UserUpdates ,
p.TableRows ,
''DROP INDEX '' + QUOTENAME(i.name)
+ '' ON '' + QUOTENAME(s.name) + ''.''
+ QUOTENAME(OBJECT_NAME(diu.object_id)) +'';'' AS ''Drop Index Statement''
FROM sys.dm_db_index_usage_stats diu
