SQL Server Indexes – How to Determine If Indexes Are Being Used

sql-server-2008

I am using SQL server, and the person creating our database added lots of indexes on our biggest table. These indexes only include one field each.

To me it seems that these indexes are mostly useless, and that this was a bad idea.

My concern is removing some or all of these indexes and affecting performance on our live system. There is no way to test this on our test system because our test system doesn't have the same load or amount of data.

Just wondering…

  • Is there a way to work out which indexes are never used?
  • Is there some kind of analysis that will show which indexes are used for what queries over a period of time for instance?

Best Answer

This is made very simple by the index usage DMV: sys.dm_db_index_usage_stats

For example, this query I use very often to identify unused indexes:

SELECT o.name Object_Name,
       i.name Index_name, 
       i.Type_Desc 
 FROM sys.objects AS o
     JOIN sys.indexes AS i
 ON o.object_id = i.object_id 
  LEFT OUTER JOIN 
  sys.dm_db_index_usage_stats AS s    
 ON i.object_id = s.object_id   
  AND i.index_id = s.index_id
 WHERE  o.type = 'u'
-- Clustered and Non-Clustered indexes
  AND i.type IN (1, 2) 
-- Indexes without stats
  AND (s.index_id IS NULL) OR
-- Indexes that have been updated by not used
      (s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );

That query is taken verbatim from sql-server-performance.com but because their blogging platform puts in em-dashes and smart quotes it renders the code on their site un-copy-pasteable. (Why? Why on a code blog??) So I put it here in a form you can use without having to resolve syntax errors.

All due credit to them for this and many other useful code snippets I rely on.

Related Question