I ran a very basic query against one of our table and I noticed that the execution plan query processor is recommending that we create an index on a column
The query is
After running, I get the following message
My problem is that I already have such index on that column:
Why is Sql Server recommending me to create such index when it already exist?
Update after Kev Riley answer
I have updated the statistics on the table by running
The Sql Server Query Analyser is now suggesting to
While I do not want to include Data with that index, I now more understand how the index map against the primary key and that it still need to scan for the other columns values.
I have never seen that exact scenario, but I have seen something fairly similar where it suggested an index on a column I already had indexed, but with included columns that I did not have in my index.
answered Mar 15 '10 at 05:04 PM
Haven't seen that before.
Are the statistics up to date on that index?
What is being used to execute the query, ie what does the execution plan say?
answered Mar 15 '10 at 04:55 PM
Kev Riley ♦♦
That index is being suggested because it's massively better that the index that doesn't include the Data column (as a Lookup would be needed). In fact, without the included column, the index may be ignored as the statistics may demonstrate that a table scan could prove quicker than doing a seek with many lookups.
Before it has updated statistics, it sees that an ideal index isn't in place, and figures that it should suggest one. But there is a bug in the system such that it values the statistics most of all, and having a new index (with new statistics) is the thing that it feels would impact it most. Unfortunately the Missing Indexes feature needs to try harder and recognize that even without updated statistics, the best index would include additional columns.
I've blogged about this before, showing how SQL doesn't always suggest the included columns. Missing indexes are useful, but always take your human eye to the recommendations as well.
answered Mar 15 '10 at 07:49 PM
Are you sure you're running the query on the same server and database that you're pulling that index from? I've never seen a missing index suggestion on a table that had an index.
answered Mar 15 '10 at 04:56 PM
Grant Fritchey ♦♦
Just remember that SQL Server makes its suggestions for each individual case without looking at your dB as a whole. And totally in isolation from your application.
If you blindly follow the recommendations given you will eventually end up with a very poorly performing system.
Make sure you test the impact of changes and document them so they can be removed if necessary. Indexes are great for efficient queries but they wreak havoc on inserts and updates. You have to balance their usage with performance.
answered Mar 15 '10 at 05:32 PM