March 15, 2010 at 10:13 am
Hi all,
I'm wracking my brains and can't figure out what's going on here.
I have a query on a SQL Server 2008 multi processor (4) instance. As it currently stands the execution plan executes a parallel plan that returns in 11 seconds. However the optimiser keeps saying there's a missing index on the main table used in the query. Here's a ((very!)simple example of the query below:
select col1,col2,col3
from table
where col4 = 'X'
Now, the table contains 2 millions rows, and the distribution of column col4 is equal (ie there are only 7 different values it can be and each is used farely evenly in the table). Yet for some reason SQL thinks I should index the predicate column col4 and include the fields (col1,col2 & col3). If I do this, the parallel plan is dropped in favour of a serial plan and the I now see a seek on "table" for col4. However, with this in place the query now runs like a dog and never returns.
What I don't understand is, why does the optimiser suggest I index such a low selectivity field? The stats are completely up to date. I've obviously decided to ignore SQL's advice but I'm perplexed why it's suggesting this.
Has anyone else had this issue?
FYI "table" has a clustered index on it on col1.
Thanks
Mike
March 15, 2010 at 10:29 am
Is the seek on the table or on the index? If you created the requested index on Col4 and included the columns Col1, Col2, Col3 it should be doing a seek on the index.
Best thing would be to post the actual execution plans for the query with and without the suggested index. This would help us tell you what is going on.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply