I have this query that takes about 2 seconds to complete. All indexes are using seeks except one scan, but this scan has the lower cost than the seeks. I have rebuilt indexes, updated column statistics, adjusted MAXDOP from 1 to 4 (4 CPUs on server) and none of those move the execution time. Just wanted to check if you see anything obvious that I am missing. Thanks.
By BrianG 1 asked Apr 24, 2017 at 05:53 PM
The only things I can see are that
(a) the compiled parameter for CustomerNumber is an explicit value and the runtime parameter is a wildcard
(b) some of the clustered index seeks could be slightly more efficient if you had non-clustered indexes that covered only the columns required, though whether it's worth maintaining those depend on workload balance. For example the clustered index seek at Node 45 could be a more efficient nc index seek if the following index were present:
(c) I don't know that parallelism is helping you here, I might shift back to maxdop 1
(d) the Table I/O tab is telling - even though the plan shows there is only a single seek on CustomerPOSCompanyXref, there's clearly more going on here, because that registers 44,611 reads. I think this is a case where the CTE messes things up, so I might try this:
Then join to #t instead of the CTE. I might be tempted to try additional unraveling of the next CTE. This may give the optimizer a better opportunity to come up with a good plan, knowing the number of company IDs it will deal with, and hopefully eliminate all of the reads that I believe are caused by the complex cascading of CTEs.
By Aaron Bertrand ♦ 1.7k answered Apr 26, 2017 at 07:23 PM