CODEWITHSUNDEEP
sql-serverindexingsql-execution-plan
Artur Udod
Artur Udod

Reputation: 4753

Why would Sql Server prefer a Clustered Index Scan over NonClustered + KeyLookup?

So here's the query and the execution plan. The commented create-index statement has already been executed. enter image description here

Why does Sql-Server decide to perform a clustered index scan? Am I missing something?

Upvotes: 2

Views: 3478

Answers (3)

TToni
TToni

Reputation: 9391

If the query would use the index, it would have to search through the index pages (at least 2), get the clustered index key from the result(s), then search with each of these keys through the clustered index (at least 2 page reads per found record) to get the rest of the record not covered by the index.

This can be an effective approach, if you have a large number of records and your where clause selects only a relative small subset of them (which the query optimizer estimates based on the index statistics).

From the looks of it you only have a small number of records in the table, maybe they even fit on one page, so the optimizers says "I can do the whole query by reading and filtering the one or two pages from the clustered index once, which is way more effective than the whole nonclustered index business in this case, so I'll do that and spare my poor overloaded server engine a whole load of trouble" :-)

Edit: Try to specify WITH (INDEX(idx_WP_Discrepancy_FilterColumns2)) before the WHERE clause and compare the estimated subtree costs in the query plan to the original query to see the difference.

Upvotes: 3

arun_bh
arun_bh

Reputation: 31

You question seems similar to this one - Why is there a scan on my clustered index?

Also check the blog mentioned in the answer for a good overview on this subject - http://www.sqlskills.com/blogs/kimberly/the-tipping-point-query-answers/

Upvotes: 3

Andomar
Andomar

Reputation: 238126

The query plan might be cached. Add:

option (recompile)

at the end of your query to force a fresh compilation.

Upvotes: 0

Related Questions