Unused Indexes Gotcha

Posted by DavidWimbush on SQL Blogcasts See other posts from SQL Blogcasts or by DavidWimbush
Published on Tue, 18 May 2010 07:06:00 GMT Indexed on 2010/05/18 14:42 UTC
Read the original article Hit count: 455

Filed under:

I'm currently looking into dropping unused indexes to reduce unnecessary overhead and I came across a very good point in the excellent SQL Server MVP Deep Dives book that I haven't seen highlighted anywhere else. I was thinking it was simply a case of dropping indexes that didn't show as being used in DMV sys.dm_db_index_usage_stats (assuming a solid representative workload had been run since the last service start). But Rob Farley points out that the DMV only shows indexes whose pages have been read or updated. An index that isn't listed in the DMV might still be useful by providing metadata to the Query Optimizer and thus streamlining query plans.

For example, if you have a query like this:

select  au.author_name

        , count(*) as books

from    books b

        inner join authors au on au.author_id = b.author_id

group by au.author_name

If you have a unique index on authors.author_name the Query Optimizer will realise that each author_id will have a different author_name so it can produce a plan that just counts the books by author_id and then adds the author name to each row in that small table. If you delete that index the query will have to join all the books with their authors and then apply the GROUP BY - a much more expensive query.

So be cautious about dropping apparently unused unique indexes.

© SQL Blogcasts or respective owner

Related posts about Tips and Tricks

Related posts about indexes