Hello SQL Server Experts. I have a table (table_A) with about 2 million rows and a table_B with about 10 million rows.
SELECT b.title FROM table_A A JOIN table_B B ON A.id = B.A_id WHERE a.last_activity > @previous_36_hours
B has an index (A_id). A has an index (last_activity [include id]). A.id is not the primary key (so it is unsorted).
The filter on A produces about 5000 rows and B will have about 12000 rows.
My execution plan without the loop hint has it scanning B.A_id without a predicate and sorting A.id to perform a merge join on A.id = B.A_id. This takes about 750ms in parallel. When I give a loop hint, it seeks on B.A_id and the time drops to 50ms non-parallel.
Is this a sign of bad sampling of data for the histogram and a sign that we need to customize the stats-- that the out of the box sampling is not working? I'm a complete novice when it comes to the finer points of this, I'm just an app developer. Obviously it is able to seek on B_id, but it refuses. The row estimates are also way off.
Personally, I can see why it might think this is a reasonable plan. It has a limited rows from A.id to sort on and B_id is already sorted. It thinks it is getting for more rows from B than it needs, so a merge join would seem like the thing to do.