Find leastly used non cluster indexes in SQL Server

Posted: October 31, 2012 in Uncategorized

SELECT objectname=OBJECT_NAME(s.OBJECT_ID)

, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes =  user_updates
, p.rows
FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i
ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID
JOIN sys.partitions p ON p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(s.OBJECT_ID,’IsUserTable’) = 1
AND s.database_id = DB_ID() �
AND i.type_desc = ‘nonclustered’
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND p.rows > 10000 AND(user_seeks + user_scans + user_lookups)< user_updates ORDER BY reads,rows DESC
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s