Do you have unused index? Do you have ineffective index? How many times was it used for select or update?
Is it used by scan , seeks or lookups?
Index usage statistics :
|
1 2 3 4 5 6 7 8 |
-- index usage statistics SELECT d.name AS DatabaseName, t.name AS TableName, i.name AS IndexName, ius.* FROM sys.dm_db_index_usage_stats ius JOIN sys.databases d ON d.database_id = ius.database_id JOIN sys.tables t ON t.object_id = ius.object_id JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE d.database_id = (DB_ID(N'DataBaseName')) and t.name='TablesName' -- Database Name and Table ORDER BY user_updates DESC |