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

Rob@MMIT 2017-12-19 18:31:03

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…

Hugo Kornelis 2017-12-20 09:20:27
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 <>);

Aaron Bertrand 2017-12-21 18:20:40
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. πŸ™‚
Hugo Kornelis 2017-12-21 18:57:14
Totally agree, Aaron. That's why I did include the link to your post. πŸ™‚
Aaron Bertrand 2017-12-21 19:04:58
πŸ™‚ πŸ™‚ πŸ™‚