Is there a way to get this to seek in the 2nd query?
I broke the query apart into a inital dump to a temp table containing the primary key values to join in the 2nd query. The temp table has a clustered primary key on the key field, and the joining table has a clustered primary key on the same field. There are only 22k records in the main table, of which I am returning 6k. I am thinking is just a matter of it being easier to scan for 6k records than seek, but wasnt sure.
It's scanning the table rather than doing 6141 seeks. Each seek would need to look at probably three levels of the index, making over 18k page reads. Your 22130 rows fit into 10MB, which is only about 1300 pages. Using these numbers, it's figuring that a scan is MUCH better. But your index is very useful, because it allows a Merge Join to be done.