Query plan shows index seek on table not referenced in the query anywhere

We are trying to tune this query and are stumped at why the query plan is showing the maximum hit twice to an index seek on a table that is not referenced anywhere in the query. We have looked at foreign keys, triggers everywhere (no views in the query) to find out where LA_BOMHeader table is used in the query. No success.

The other 2 issues that jump out are the table heaps (3rd Party tables in Great Plains) and another table heap(clustered index scan) that returns almost 2 million rows. That one, we feel the statistics are upto date, but still showing huge disparity between estimated and actual rows. Is it possible that the stats are correct, but we have such a huge variation?

What could be the best line of attack in tuning this monster query? Thank you.

On further investigation, turned out the BOM50100 is a View that references the LA_BOMHeader table! So that part of the mystery is solved. Sorry about the earlier confusion.

avatar image By Yaraj 37 asked Jul 22, 2015 at 02:59 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

...and another table heap(clustered index scan) that returns almost 2 million rows. That one, we feel the statistics are upto date, but still showing huge disparity between estimated and actual rows. Is it possible that the stats are correct, but we have such a huge variation?

The section of the plan is:

2 million row scan

The 1,833,168 rows is a total over 8,688 iterations of the scan. There is a poor estimate at the Filter: 2,798 rows actual versus 1 estimated. This is compounded by the two intermediate joins, so the scan is performed 8,688 times versus the optimizer's estimate of 5. Assuming correct indexing, the optimizer would never have chosen the scan if it had known about the 8,688 rows.

The separate Filter looks odd. Unless BOMID is a large object type (like varchar(max)), the BOMID = BOMID condition in the filter really ought not to be separate at all. Ideally, it would form part of the seeking condition in the join immediately prior to the Filter. Perhaps there is something in the view that is preventing this predicate being pushed down correctly. In any case, you should review the column type, indexing and view definition to determine why you're getting the separate filter here. Eliminating that should also improve the estimate.

The Clustered Index Scan is also something you should look at. Instead of a residual predicate on the nested loops join on the ITEMNMBR columns, we would normally hope to see an apply-seek here, with ITEMNMBR as an outer reference.

Ensure there is a suitable index on the scanned table. From the plan, an ideal index would be:

 CREATE INDEX ix_name
 ON dbo.SOP10200
     (ITEMNMBR, SOPTYPE, ReqShipDate)
 INCLUDE
     (QTYFULFI, QTYREMAI, SOPNUMBE, QTYBAOR)
 WHERE
     QTYBAOR > 0;

You should obviously review that idea against the existing indexes and in the context of your workload as a whole before deciding to implement it.

What could be the best line of attack in tuning this monster query?

The first thing I would do is to lose the CTEs. CTEs are NOT temporary tables. Repeated references to the same CTE generally result in duplicate parts of the finished plan - perhaps you have noticed this already.

Anyway, replace the CTEs with proper temporary tables as a first step. This will break the 'monster' into smaller parts, which should make it easier for you to identify missing/incorrect indexes and other tuning opportunities. Using temporary tables will also give the optimizer better statistical information to work with.

sp.png (43.2 kB)
avatar image By SQLkiwi ♦ 6.6k answered Jul 23, 2015 at 05:39 AM
more ▼
(comments are locked)
avatar image Yaraj Jul 23, 2015 at 03:55 PM

Thanks for the detailed analysis and response. The take on CTEs and temp tables was new to me. Will look at all these suggestions and get back. Thanks again

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x46
x24
x3

asked: Jul 22, 2015 at 02:59 PM

Seen: 98 times

Last Updated: Jul 23, 2015 at 03:55 PM