Query optimizer is doing index scan instead of seek

N 2016-09-19 14:46:44

I have a table A with 52 million rows and clustered index is on primary key which is an integer.

A(id int, colA, ColB, Col C, Col D, Col E)

I am trying to query it using temp table t with 14 million rows. I am getting an index scan in the plan instead of seek.
T(id, colG, ColH, ColI)

Query is

Select count(*) from a inner join t on a.id=t.id

When I look at the plan I see an index scan on table a instead of seek. The stats are updated. I ran a full scan.

Aaron Bertrand 2016-09-19 14:57:44
How many rows end up being returned by the final query? How many rows actually need to be read in order to return those rows? Why do you think a seek will be better? (A seek isn't always better – in fact sometimes it can be much, much worse.)
N 2016-09-19 15:01:24
I am expecting it to return 14 million rows. I agree that seek is not always better but not sure if scan is better when it is returning around 25% of data.
Aaron Bertrand 2016-09-19 15:02:37
Probably not, but it depends (some of it on details you didn't include, like we would get from an actual plan).
N 2016-09-19 15:04:04
Ok.. I will try to get a plan…
Aaron Bertrand 2016-09-19 15:06:56
Tipping point (when SQL Server determines that a scan will actually be better) is generally between 25-30% of the table, but depends on many other factors as well, such as how wide the rows are, whether lookups or other per-row operations are required, etc. There are a few articles out there on tipping point, my favorites are by Kimberly Tripp and Klaus Aschenbrenner.
N 2016-09-19 15:08:50
Many thanks.. I will read through them and will share the plan as well soon.
Aaron Bertrand 2016-09-19 15:10:14
You can also try two copies of the query – one with the plan SQL Server produced, and one where you try to force a seek (using the FORCESEEK hint). If you generate actual plans from those and compare, you should see why SQL Server chose the scan – there's usually at least one very obvious reason (often it is a high cost of lookups).