SQL Server Performance Forum – Threads Archive
Too many indexes
Hi. I’m quite new to MS SQL Server 2005. We have a table in production that has a few indexes. I suspect that not all of the indexes are used and then slowing down inserts and updates. How can i identify unused indexes? Thanks,EBcheck this:
http://www.sql-server-performance.com/lm_index_elimination_english.asp
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9613&whichpage=1
In 2005, check out this new DMV: sys.dm_db_index_usage_stats. —————————–
Brad M. McGehee, SQL Server MVP
Here is the query to get the indexes not being used… SELECT so.name AS ‘Table Name’, si.name AS ‘index Name’, si.type_desc AS ‘Index Type’, si.index_id AS ‘Index ID’,
us.user_seeks, us.user_scans, us.system_seeks, us.system_scans, us.user_lookups, us.system_lookups
,us.user_updates, us.system_updates
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.objects so
ON us.object_id = so.object_id
INNER JOIN sys.indexes si
ON so.object_id = si.object_id
WHERE so.type = ‘U’
AND us.user_seeks = 0
AND us.user_scans = 0
AND us.system_seeks = 0
AND us.system_scans = 0
UNION
SELECT so.name AS ‘Table Name’, si.name AS ‘index Name’, si.type_desc AS ‘Index Type’, si.index_id AS ‘Index ID’,
‘0’,’0′,’0′,’0′, ‘0’,’0′,’0′,’0′
FROM sys.objects so
INNER JOIN sys.indexes si
ON so.object_id = si.object_id
LEFT JOIN sys.dm_db_index_usage_stats us
ON si.object_id = us.object_id
AND si.index_id = us.index_id
WHERE us.object_id IS NULL
AND so.type = ‘U’
ORDER BY so.name, si.index_id MohammedU.
Moderator
SQL-Server-Performance.com
One thing to note is that the information available in sys.dm_db_index_usage_stats is only from the last restart of the server. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
If you are in the process of analysis, it is advisable to copy the sys.dm_db_index_usage_stats data to a permanant table because most of the DMVs carry the data from the last restart as Roji mentioned…
MohammedU.
Moderator
SQL-Server-Performance.com
]]>