SQL Server 查詢索引的使用方式

標籤：

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable‘) = 1
AND s.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1 --and OBJECT_NAME(s.[object_id])=‘Lot‘
ORDER BY [Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;

--執行結果

--結果描述，只介紹倒數2,3列， Total Write 與 Total Reads

如果 Total Reads 等於 0，說明索引沒起作用，建議刪除當前索引

SQL Server 查詢索引的使用方式