x

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

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.

more ▼

asked Mar 15, 2010 at 04:50 PM in Default

Pierre-Alain Vigeant gravatar image

Pierre-Alain Vigeant
25 1 1 2

(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

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.

more ▼

answered Mar 15, 2010 at 05:04 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

I'm glad that picked as the answer & all, but, am I wrong in seeing this situation as totally different?
Mar 15, 2010 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, 2010 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, 2010 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, 2010 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, 2010 at 09:30 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Mar 15, 2010 at 04:55 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 15, 2010 at 07:49 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 15, 2010 at 04:56 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.7k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 15, 2010 at 05:32 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x128
x6

asked: Mar 15, 2010 at 04:50 PM

Seen: 10796 times

Last Updated: Mar 15, 2010 at 05:09 PM