Why does Sql Server recommends creating an index when it already exist?

Question

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

SELECT SUM(DATALENGTH(Data))
FROM Item
WHERE Namespace = 'http://some_url/some_namespace/'

After running, I get the following message

// The Query Processor estimates that implementing the following index could improve the query cost by 96.7211%.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Item] ([Namespace])

My problem is that I already have such index on that column:

CREATE NONCLUSTERED INDEX [IX_ItemNamespace] ON [dbo].[Item] 
(
    [Namespace] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

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 UPDATE STATISTICS.

The Sql Server Query Analyser is now suggesting to INCLUDE a column. This is a syntax that I did not know:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Item] ([Namespace])
INCLUDE ([Data])

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.

Answer 1

0

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

TimothyAWiseman
14.3k ● 16 ● 20 ● 29

I'm glad that picked as the answer & all, but, am I wrong in seeing this situation as totally different?

Mar 15 '10 at 05:10 PM Grant Fritchey ♦♦

Your comment about "including columns" along with the idea that Kev Riley gave me is what made me understand that you can include columns in an index.

Mar 15 '10 at 05:18 PM Pierre-Alain Vigeant

@grant - no you're not - unless the original question wasn't complete - but it sounds like the op was accurate in what he said

Mar 15 '10 at 05:28 PM Matt Whitfield ♦♦

Oh, the index suggestion posted above is not complete. SQL Server was recommending an index with an INCLUDE column. Well, then that was a good catch Timothy, especially based on incomplete information.

Mar 15 '10 at 05:37 PM Grant Fritchey ♦♦

@Grant I wasn't sure if that was the situation or not, but it was the closest I had ever seen before.

Mar 16 '10 at 09:30 AM TimothyAWiseman

(comments are locked)

Answer 2

0

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 ♦♦
46.1k ● 38 ● 43 ● 69

(comments are locked)

Answer 3

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.

Answer 4

0

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 ♦♦
62.6k ● 12 ● 20 ● 66

(comments are locked)

Answer 5

0

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

Blackhawk-17
10.5k ● 23 ● 29 ● 34

(comments are locked)

Why does Sql Server recommends creating an index when it already exist?

Follow this question

Related Questions