4

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?
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
peter
  • 2,187
  • 5
  • 19
  • 38

2 Answers2

6

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.

Jonathan Van Matre
  • 415
  • 2
  • 11
3

Your intuition serves you well, they are indeed probably useless.

You can confirm whether they are being used or not via the sys.dm_db_index_usage_stats DMV. Kimberly Tripp's 'Spring Clean Your Indexes' articles are as good a place as any to start.

Mark Storey-Smith
  • 31,687
  • 8
  • 89
  • 124