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.

  • Is A.id included or in the key of the index on a.last_activity? What happens if you manually update statistics? Can you post the two actual, post-execution plans somewhere? – Aaron Bertrand Jan 30 '15 at 14:23
  • Sorry, yes, A.id is included and I've edited the question. For the exec plans, I guess you would want the text version? – J.T. Jan 30 '15 at 14:52
  • 1
    You might consider moving id to the key to see if that makes a difference in supporting the join. And no, not text versions of the plan please, I didn't bring my bifocals today. .sqlplan. – Aaron Bertrand Jan 30 '15 at 15:00
  • "I didn't bring my bifocals today" -- hahaha – J.T. Jan 30 '15 at 15:12
  • @J.T., is @Previous_36_hours a parameter or local variable? – Dan Guzman Feb 1 '15 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.