September 10, 2009 at 7:25 am
Is thre an easy way to identify which index is used very frequently on a production database, i am in the process of eliminating un unsed indexes and so need help.
I understand this involves lot of things to research on index but to start with how would i identify indexes which are regularly used.
September 10, 2009 at 7:29 am
These quereis might help. But bear in mind that these work on data since the last time SQL Server was restarted, so if that was recently, the queries might not be very helpful.
--Unused indexes and tables
SELECT OBJECT_NAME(i.OBJECT_ID) AS ObjectName
, i.NAME AS IndexName
, i.index_id
, (SELECT MAX(rows) FROM sysindexes WHERE id = o.OBJECT_ID) AS record_count
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.OBJECT_ID = i.OBJECT_ID
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.OBJECT_ID=s.OBJECT_ID
AND i.index_id=s.index_id
AND database_id = DB_ID()
WHERE OBJECTPROPERTY(o.OBJECT_ID,'IsUserTable') = 1
AND s.index_id IS NULL
AND i.NAME IS NULL
ORDER BY 4 DESC, ObjectName,i.index_id,IndexName ASC
--Index usage. Least used appear first.
SELECT OBJECT_NAME(s.OBJECT_ID) AS ObjectName
, s.OBJECT_ID
, i.NAME AS IndexName
, i.index_id
, user_seeks
, user_scans
, user_lookups
, user_updates
, (SELECT MAX(rows) FROM sysindexes WHERE id = i.OBJECT_ID) AS record_count
, 'drop index ' + OBJECT_NAME(s.OBJECT_ID) + '.' + i.NAME + ';' AS drop_command
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.OBJECT_ID = s.OBJECT_ID
AND i.index_id = s.index_id
WHERE database_id = DB_ID ()
AND OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND i.NAME IS NOT NULL
ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC
September 10, 2009 at 7:35 am
Check out the dmv sys.dm_db_index_usage_stats - that should give you pretty much exactly what you're looking for. Of course, you'll need to do some extra work to track down which indexes are which (since they're listed by object ID in the dmv), but this script (from Louis Davidson on SQLBlog) should either give you what you need, or at least a good starting point:
--returns all indexes for a database and their stats.
--Rows with no usage since the last restart will be null
select object_schema_name(indexes.object_id) + '.' + object_name(indexes.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc,
ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates
from sys.indexes
left outer join sys.dm_db_index_usage_stats ddius
on indexes.object_id = ddius.object_id
and indexes.index_id = ddius.index_id
and ddius.database_id = db_id()
order by ddius.user_seeks + ddius.user_scans + ddius.user_lookups desc
Edit: forgot my code tags. And I was beaten to the punch, anyway. 😉
______
Twitter: @Control_Group
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply