4

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?

Improve this question
2
  • 3
    Anonymizing the code like that (Schema01.Object02.Column03) makes things harder to follow visually IMO, and may reduce the number of people willing to make the effort to help. It is certainly why I didn't look in any more detail than I have in my answer, which I otherwise may have done. I would recommend using real object names unless they absolutely positively unequivocally have to be kept super duper "if I told you, I'd have to kill you" secret.
    – David Spillett
    Oct 29, 2019 at 15:14
  • Safety first! I get what you're saying, but I doubt my company would go for that. Appreciate your responses regardless.
    – BBaggins
    Oct 30, 2019 at 12:52

2 Answers 2

Reset to default
4

Here are stats of two plans:

  1. Old plan: You've got a 1,864 memory grant, estimated number of rows 400
  2. 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

old plan

New 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 :

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

Old plan filter

New plan filter

New plan filter

Improve this answer
6
  • Thanks. These were actual execution plans.
    – BBaggins
    Oct 29, 2019 at 15:38
  • Row count the same for both.
    – BBaggins
    Oct 29, 2019 at 15:39
  • @BBaggins well then look at the last SELECT on the execution plan. SQL is estimating 43017 rows in the second query and 1864 on the first one. So either your statistics are not up to date, or something makes SQL guess the wrong number. If you can fix that, then maybe the second plan could be better than the first. but as of now, the second one takes way to much memory compared to the first one.
    – Danielle Paquette-Harvey
    Oct 29, 2019 at 16:59
  • Stats are updated every night. The old plan is vastly underestimating row counts and for some reason swapping out that INNER JOIN for a WHERE EXISTS results in way less IO on this thing (20K page reads as opposed to 380K), but the new plan costs nearly twice the old and seems significantly less memory-efficient. At a high level, what other factors might account for the new plan being "worse" when it's doing so many fewer reads?
    – BBaggins
    Oct 30, 2019 at 12:58
  • 1
    Thanks for the feedback! I'll run these again and see about spills to tempdb. Memory grants are a weak spot in my tuning game. Will also read this BO post.
    – BBaggins
    Oct 30, 2019 at 18:08
1

if this new plan ... is superior ...

It depends on how many rows end up in the temporary table and how many rows end up being compared to it. The JOIN approach might force the query planner to reference the temp table for every combination considered then filter by the other parts of the query, where WHERE EXISTS is more likely to use the temporary table later in the process.

Unless there are very few rows in the temporary table I would suggest that neither option is ideal as there are no indexes on the temporary tables which might allow the query planner to produce a more efficient process.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.