I feel like the Clustered Index Scan on [PK_Cash In Reconciliation Fact Inventory] should be a seek and only have to read 11 or so million rows rather than the 2 billion rows that it does with the scan.
Can anyone explain why it is performing a full index scan or help me get a seek out of this plan?
The query ran for 2 days and was only 28% complete. We need this to run in less than 24 hours.
By SQLHammer 83 asked Nov 07, 2016 at 04:14 PM
If a seek is possible, you can force it with a
If the optimizer chose a hash join with bitmap over a nested loops join, it did so for cost reasons. Costing estimates can be wrong, especially for parallel nested loops joins plans that tend to execute faster in practice than the model assumes, due to improvements in hardware since the model was implemented, and the way costs are adjusted for nested loops (i.e. not at all on the inner side).
Nested loops can also be significantly worse than hash join. It depends on the number of CPUs, data distribution, and the number of pages that must be brought in from persistent storage (read-ahead is significantly less effective on the inner side of a nested loops join than for a sequential scan).
Note the scan has an
That said, the Sort is also likely to be a large runtime cost, especially if insufficient memory is reserved or available. It seems like the source and target Fact tables have different partitioning schemes. The Sort looks to reduce the number of expensive partition switches at the target, by sorting rows in partition ID order.
There might be opportunities to improve on the plan, but this requires deeper knowledge of the schema and the task you are performing. If you are interested in exploring that, please add the table and index DDL for the three tables to your question (as an attachment if it is large) and include the partitioning function and scheme definition(s).
It would also be helpful if you would describe the overall process a bit, and explain in more detail why the
By SQLkiwi ♦ 6.6k answered Nov 07, 2016 at 06:18 PM
I have noticed few interesting things in the query, assuming this is same as what runs in production
I would suggest trying to replace original where clause
By Vlady Oselsky 156 answered Nov 07, 2016 at 08:08 PM