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.
---------- 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