tony28 (5/5/2014)
Hello,does somebody know query for result about index where procedure or query use this index ?
You can check that by query plan
Below query give you all non clustered index usage of tables..
SELECT OBJECT_NAME(ddius.[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 ddius WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON ddius.[object_id] = i.[object_id]
AND i.index_id = ddius.index_id
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
AND ddius.database_id = DB_ID()
-- AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;
and this one will give you particular table's index
SELECT s.name, D.*
FROM sys.dm_db_index_usage_stats D
LEFT OUTER JOIN sys.indexes S
ON D.object_id = S.object_Id AND D.index_id = S.index_id
WHERE database_id = DB_ID('DBname') AND d.object_id = OBJECT_ID('tablename');