Which Plan is Better? WHERE EXISTS Instead of INNER JOIN

Did a switch to a WHERE EXISTS in temp table (Object13 in the plans) from an INNER JOIN to said temp table in final select in procedure.

Logical reads plummeted, but plan cost and memory performance seem worse. Duration and CPU are slightly better with the WHERE EXISTS

Curious if this new plan (with the WHERE EXISTS) is superior to the one with the INNER JOIN. If so - why technically would a WHERE EXISTS would be a better option?

• Old Plan

• New Plan

Here are stats of two plans:

• Old plan: You've got a 1,864 memory grant, estimated number of rows 400

• New plan: You've got a 69,056 memory grant, estimated number of rows 43,017

So we have two options:

• Either those are estimated execution plans and if so, you should run the queries and get the actual execution plan

• Either those two queries don't return the same amount of rows and if so, they are not equivalent

In order to compare right, you should try checking statistics on your tables also, and run the queries with the option recompile to make sure it doesn't take an inappropriate plan in cache.

Old plan



New Plan

---------- EDITED TO EXPLAIN THE DIFFERENCE OF MEMORY GRANT --------------

Here is an important detail that I missed the first time I looked at your plans :

• Old plan: The estimated number of rows is 400 but the actual number of rows is 29899. Since SQL only did a memory grant of 1864 for 400 rows, it probably spilled to TempDb.

• New plan: The estimated number of rows is 43017 but the actual number of rows is 29904. So SQL did a memory grant of 69056 for 43017 rows, but only needed the memory for 29904 rows. So in this case, it took too much memory.

So in both case, the number of rows is almost the same. To know which one is best is a tricky question and will depend on your hardware and your server. If I/O is a problem and tempDB is under high utilization, then the second one could fix some problems for you. But if your server is under memory pressure, then the first one is better.

In order to better understand the two options, you should see this great blog by Brent Ozar about memory grant

Old plan filter

New plan filter

StackExchange Database Administrators Q#252140, answer score: 4