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

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.

Thanks

avatar image By sreesql 16 asked Oct 07, 2014 at 06:59 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first
  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:

http://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx

avatar image By SQLkiwi ♦ 6.6k answered Oct 08, 2014 at 05:45 AM
more ▼
(comments are locked)
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:

x631
x166
x1
x1
x1

asked: Oct 07, 2014 at 06:59 AM

Seen: 124 times

Last Updated: Oct 08, 2014 at 05:45 AM