June 10, 2009 at 8:25 am
With a long uptime, I checked the index use and would like to drop the indexes which have zero user_seeks/scans & lookups. Such indexes are not used and are wasting performance with many index updates.
Just to be (newbie)sure, I would like to have the statement to recreate the dropped indexes. Maybe anyone know of such a script?
The script I use to check my indexes and also output the 'drop index' cmd is following:
Why I not just disable? Because if i'm correct an index rebuild will recreate the disabled indexes & our maintenance plans have such rebuilds included.
Rgds 2 all & thx for any help/comment.
T.
SELECT o.name AS object_name, i.name AS index_name
, i.type_desc, u.user_seeks, u.user_scans, u.user_lookups
, u.user_updates, u.last_user_seek, u.last_user_scan
, 'Drop index [' + i.name + '] on [' + o.name + ']' as DropIndexStatement
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
AND i.index_id = u.index_id
AND u.database_id = DB_ID()
WHERE o.type <> 'S'
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY (convert(decimal(19,4),ISNULL(u.user_seeks, 0))
+ ISNULL(u.user_scans, 0)
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc
, user_updates desc, o.name, i.name
June 10, 2009 at 8:31 am
T2000 (6/10/2009)
With a long uptime, I checked the index use and would like to drop the indexes which have zero user_seeks/scans & lookups. Such indexes are not used and are wasting performance with many index updates.
Be careful. The index usage stats DMV only contains info since the last start of SQL Server. I strongly suggest that you don't just go and drop all 'unused' indexes without checking that they are indeed unused. Dropping a couple indexes needed for the month end/year end run is generally not a great idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2009 at 8:38 am
i understand. The uptime now is about 7months. I presume this is long enough + fact the index usage = zero.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply