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