Why is this query causing the execution plan to include tables NOT in the query itself?

Haven't seen this issue before. Execution plan is including tables not in the query, which is running out of control. Inherited this as part of a bigger project and will rewrite, but would like to understand what's going on here...

avatar image By Rob@MMIT 0 asked Dec 19, 2017 at 06:31 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

In generic, there can be two possible explanations for this.

One is that the table names in your query are actually views. I checked your execution plan and that is not the case. The part where the new data is collected references exactly the tables I see in your query.

The other is constraint checking. This happens in any plan that modifies data. You use MERGE, so you do modify data.

Most of the branches go into other tables and then delete data. This suggests that you have a lot of foreign key constraints with cascading deletes.

A few of the branches go into other tables (but lots of them!) and end with an assert. The assert operator is intended to force execution to end with an error if a condition appplies. So in these branches, foreign keys with ON DELETE NO ACTION (the default)

Now that you have an explanation, you may be wondering how to fix this. Problem is, you can't. Sure, you can disable all those foreign key constaints but then you will get invalid and inconsistent data in your database. Plus, for mny OTHER queries those FKs actually help to give you better performance. (But obviously, the correctness argument always trumps performance considerations).

What you can do, if this query is causing issues, is to batch the action (run multiple times for limited-size batches of rows). Since this query only deletes data it is fairly simple to do this: add a TOP(xxx) (with a suitable number for xxx), then enclose this in a WHILE loop that runs until @@ROWCOUNT equals zero. You may also want to have a CHECKPOINT statement in the loop, and perhaps even a BACKUP LOG.

(But if you run this merge statement inside a larger transaction, then the benefit of using such a loop becomes minimal)

Finalyl, since you are using MERGE I feel the need to point you to this warning. There are issues with merge. Most of them can be circumvened if you know what you are doing so I personally don't agree with the suggestion to avoid merge completely. However, in your case you are only doing a delete when not matched by source, so it would be trivial to rewrite this as DELETE FROM FormulariesByMonth WHERE MonthId = @MonthId AND NOT EXISTS (SELECT * FROM <>);

avatar image By Hugo Kornelis 271 answered Dec 20, 2017 at 09:20 AM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Dec 21, 2017 at 06:20 PM

Fair, though it's hard to add a disclaimer to such a warning, like "unless you know what you're doing." Everybody thinks they know what they're doing. If you know what you're doing, you know the warning is not for you. :-)

avatar image Hugo Kornelis Dec 21, 2017 at 06:57 PM

Totally agree, Aaron. That's why I did include the link to your post. :)

avatar image Aaron Bertrand ♦ Dec 21, 2017 at 07:04 PM

:-) :-) :-)

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.

We are Moving!


Follow this question



asked: Dec 19, 2017 at 06:31 PM

Seen: 50 times

Last Updated: Dec 21, 2017 at 07:04 PM