Updated Unused Index Query

Posted by Jason on Tuesday, January 13, 2009 to SQL Server 2008, SQL Server 2005, SQL performance tuning, tsql, Indexes
3330 Views | 4 Comments |

I have cleaned up and updated my unused index query. It returns a list of indexes ordered by ascending reads. If the instance has been up for a long time, it is probably safe to drop indexes with zero or close to zero reads. If the reads are low and the writes are high, this may help improve your transactions per second count. Otherwise, you are just cleaning up unused space. Use caution though. A missing index is worse than an unused index.

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
ORDER BY reads, rows DESC

I will also be putting this query up at the SSP WIKI.

email it! | |

Permalink

4 Comments

Rate this Post: 12345
COMMENTS:

A useful addition to that query would be Schema Name

posted @ Tuesday, January 13, 2009 8:31 PM by scott herbert

Jason, Thanks a million! Or several million in a some cases. I will proceed with caution, but in just a few minutes it was apparent that there is quite a bit of unnecessary overhead on some of our systems.

Do you have any other pearls like this?

posted @ Saturday, January 17, 2009 1:49 PM by Sean Decker

I keep my secret sauce @ http://sqlserverpedia.com/wiki/Transact_SQL_Code_Library

posted @ Saturday, January 17, 2009 1:58 PM by JasonMassie

Comments from the following blog entry: http://www.brentozar.com/archive/2009/01/more-sqlserverpedia-syndicated-bloggers/

posted @ Friday, January 30, 2009 7:33 AM

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]

Home|Upcoming Events|Feeds | Privacy Statement | Terms Of Use