DELETE using INNER JOIN vs EXISTS: which one is better for this scenario

sreesql 2014-10-07 06:59:37

Both the queries are run multiple times, on multiple days and in no particular order (cleaning buffers is not an option in my test environment). The questions are: (1) why is the TOP operator in DELETE using INNER JOIN estimates wrong number of rows and what actually is the TOP operation here (2) why are all the TABLE SPOOL branches for the non-clustered indexes (index delete) missing for DELETE using INNER JOIN plan (3) which query is actually better performance for this situation. espeically with much higher memory grant for DELETE using EXISTS clause. I was thinking the EXISTS might be performing better in general, but I could be wrong.


SQLkiwi 2014-10-08 05:45:12
1. The TOP operator is a rowcount top; it enforces SET ROWCOUNT at execution time. This operator does not appear in SQL Server 2012 or later (rowcount is still effective, though deprecated, it is just handled differently). It is possible the plan was cached when ROWCOUNT was set to 1; alternatively it may be just an ordinary estimation error. The different estimate leading into the Top may have been computed in a different way. There is no guarantee that estimates will 'make sense' across the whole plan in general.
2. Given the low expected number of rows, the optimizer chose to maintain nonclustered indexes at the same time as the clustered index. Plan Explorer makes this clear by highlighting the +6 nonclustered indexes in green.
3. Only you can measure this.

For more information about estimates and narrow versus wide update plans, see the following article: