[SQL]找出DB中沒有使用或成本高的索引資訊

2013-02-07

9062
0
SQL

如果都沒有用過的索引，也會影響儲存空間及效能。所以我們將透過sys.dm_db_index_usage_stats來找出，從DB Server啟動以來，從來都沒有用過的索引資訊。

在「找出DB中遺漏索引的資訊」一篇中，可以找到DB中遺漏索引的資訊。

那如果都沒有用過的索引，也會影響儲存空間及效能。

所以我們將透過 sys.dm_db_index_usage_stats 來找出，從DB Server啟動以來，從來都沒有用過的索引資訊。

透過user_updates(因為資料表有做INSERT/UPDATE/DELETE所造成索引更新的次數)來找出前20個影響較大的索引資訊，如下，

SELECT TOP 20
	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]
	, s.system_seeks 
	, s.system_scans 
	, s.system_lookups
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;

以上是針對目前的DB查詢，如果要一次找多個DB的話，可以透過 sp_MSforeachdb 和 temp table來儲存所有DB的索引資訊，如下，

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]
	, s.system_seeks 
	, s.system_scans 
	, s.system_lookups
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]
	, s.system_seeks 
	, s.system_scans 
	, s.system_lookups
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 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC

DROP TABLE #TempUnusedIndexes

如果認為DB Server啟動以來，系統大多數的功能都使用過了，那就可以將上面找出的索引刪除或是停用，以減少儲存空間及提升效能。

MSDN user_updates 的說明如下，
會指出索引上由基礎資料表或檢視的插入、更新或刪除作業所導致的維護層級。
您可以利用這份檢視來判斷應用程式根本很少用到的索引。
您也可以利用這份檢視，來判斷哪些索引會產生維護負擔。
您可能會考慮卸除產生維護負擔，但不用於查詢，或者不常用於查詢的索引。

所以也可以透過 user_updates - user_seeks - user_scans - user_lookups 的計算，也可以找出成本比較高的索引是那些，如下，

SELECT TOP 20
	 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