I am using the DMV views to get a list of index recommendations and then I am reviewing each of them to make sure they make sense and don't overlap etc.

Some examples are

Index ON X ( Y ) and Table X already has the INDEX Y. So far this is for non clustered indexes and the indexes have been defragmented.

SQL Server 2008 R2, database is fairly busy and lots of index recommendations are coming up. Right now it's hard to see the wood for the trees; I am just trying to get some low hanging fruit to start with.

I have checked the last access times and they're all recent.

SQL Server version I'm using is:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) Jul 9 2014 16:04:25 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: )


SQL Server 2008 and 2008 R2 have known issues in the index-recommendation code that were fixed in SQL Server 2012.

Having said that, index recommendations should always be looked at carefully to see if there is (1) an existing index that might be slightly modified to handle it's current load along with the new load, and (2) if any changes can be made to the query producing the recommendation so it uses an existing index.

Paul Randal wrote about the bug in SQL Server 2008 here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.