September 12, 2011 at 4:16 pm
Good day;
I was in the mid of writing a stored procedure where the SQL query engine suggested me to define an index that it evaluated to be missing. It was basically a foreign key that was being used in an inner join as well as being matched via an input parameter. After defining the index on the foreign key (as the engine suggested) the resulting execution plan showed a key look up. I have been reading online articles that suggest to remove look ups therefore I went ahead and defined a new index that had the primary key of the table as well as included the columns that were being used either in the join or output, and deleted the index that was suggested by the engine. The execution plan now used my index (50%) and showed a non-clustered seek.
I did not quite get this, I have been reading all this while that Seek is always performed on a clustered index. Can anyone please explain this? Let me know if I should include the table schema over here.
Regards
Kazim
September 12, 2011 at 4:43 pm
Seeks can be on any index at all. That's the main reason for having an index.
It's a key lookup that's always to the cluster.
p.s. To eliminate key lookups you don't define new indexes, you widen the index that the query is using to include the columns being looked up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2011 at 6:31 am
I agree with you on the key look up bit, I did widen the index making it to cover all those attributes that were being hit.
Can you refer me any good post that sheds some light over the index seek with non clustered? I tried Googling it but the posts doesn't explicitly talk about this topic in particular.
Regards
Kazim
September 13, 2011 at 8:22 am
Not sure what you're looking for, but an index seek on a nonclustered index is just called an index seek (as opposed to a clustered index seek) and is the main reason one puts nonclustered indexes in place.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply