How to track down bad indexes in SQL Server 2005

One of the ongoing tasks for a DBA with a high volume OLTP database is to determine the best indexes for your workload. In an OLTP environment, you need to be careful about not over indexing your tables, because you may end up paying more to maintain your indexes than the benefit you get from them. Before SQL Server 2005, it was extremely difficult to determine whether an index was being read from more than it was being written to.
 
Now, with SQL Server 2005, you can use a Dynamic Mangement View (DMV) query to find indexes that may be good candidates for deletion.
If you query sys.dm_db_index_usage_stats (like you see below), you will get a list of possible bad indexes that you might want to delete.
 
— Possible bad Indexes (writes > reads)
 DECLARE @dbid int
 SELECT @dbid = db_id()
 SELECT ‘Table Name’ = object_name(s.object_id), ‘Index Name’ =i.name, i.index_id,
     ‘Total Writes’ =  user_updates, ‘Total Reads’ = user_seeks + user_scans + user_lookups,
   ‘Difference’ = user_updates – (user_seeks + user_scans + user_lookups)
 FROM sys.dm_db_index_usage_stats AS s
 INNER JOIN sys.indexes AS i
 ON s.object_id = i.object_id
 AND i.index_id = s.index_id
 WHERE objectproperty(s.object_id,’IsUserTable’) = 1
 AND s.database_id = @dbid
 AND user_updates > (user_seeks + user_scans + user_lookups)
 ORDER BY ‘Total Writes’ DESC, ‘Total Reads’ ASC
 
This only works if you are in 90 compatibility mode, and you need to be aware that these stats are cleared out when SQL Server is restarted.
Just because an index shows up in this query does not mean you should automatically delete it, but you should definitely investigate it further.
 

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

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