SQL索引使用效率

SQL SERVER往往有一堆索引，但是如何了解這些索引有正常運作? 可以透過語法了解

主要Table

sys.object

查詢各Table的Object_Id

sys.indexes

查詢每個Index的名稱、方式(HEAP、CLUSTERD、NONCLUSTERED)

sys.dm_db_index_usage_stats

每個Index的使用率

SELECT
	OBJECT_NAME(I.object_id) AS table_name, 
	I.name AS index_name, 
	S.user_seeks, 
	S.user_scans, 
	S.user_lookups,
	S.user_updates,
	I.type_desc,
	CASE 
		WHEN last_user_seek is not null then last_user_seek 
		WHEN last_user_scan is not null then last_user_scan 
		else last_user_update
	END as user_time
FROM 
sys.objects AS O
LEFT JOIN sys.indexes AS I ON O.object_id = I.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS S ON S.object_id = I.object_id AND S.index_id=I.index_id
WHERE O.name = 'NineYiSalePage'
ORDER BY user_time desc;

查詢範例

欄位說明：

• user_seeks：這個索引被使用在 index seek 次數
• user_scans：這個索引被使用在 index scan 次數
• user_lookups：這個索引被使用在 index lookup 次數
• user_updates：索引更新次數
• last_user_seek：上一次執行 index seek 的時間
• last_user_scan：上一次執行 index scan 的時間
• last_user_lookup：上一次執行 index lookup 的時間

在查詢結果中，你應該關注的是：

• 若 user_seeks 或 user_scans 值都很低，表示這個索引很少在使用。
• 若 user_seeks 很低， user_scans 很高，表示這個索引不是一個好的索引，應該可以進一步重新規劃。
• 若 user_updates 很高，可以檢查看看，是否有不必要加入索引的欄位。

找到MSSQL 索引破碎的問題

 SELECT OBJECT_NAME(dt.object_id),si.name,dt.avg_fragmentation_in_percent,dt.avg_page_space_used_in_percent
FROM
       (SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
       FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')
       WHERE   index_id <> 0
       ) AS dt 
       INNER JOIN sys.indexes si
       ON     si.object_id = dt.object_id
          AND si.index_id  = dt.index_id

出處: VITO の 學習筆記: 建立索引(1)-叢集與非叢集索引 (vito-note.blogspot.com)