I have a very complex execution plan and below is part of it, opened in Plan Explorer. The image is kind of faded, I guess it's because all operators produce 0 rows.

As you can see, the arrow 1 is a clustered index seek, which produces 0 record. Why the the hash match operator (arrow 3) still has 53% cost? As I understand, hash match builds the hash table based on the output of the upper operator (which is 0 row in this case). After that it get all rows from the lower operator (arrow 2) and probes in the hash table. But in this case, they are all 0 rows. Why it still has such a high cost 53.4%?

  • 7
    Costs are pre-execution estimates. Stop looking at them. They’re mostly useless. Commented Oct 15, 2022 at 7:14
  • @ErikDarling Then where should I start finding the most consuming operator to tune? Commented Oct 15, 2022 at 7:42
  • 2
    You should run the query and get the actual execution plan and focus on operators that consume the most time. Commented Oct 15, 2022 at 8:14
  • 3
    Relevant blog posts are standing by: What To Do When Your Query Runs For Too Long To Get An Actual Execution Plan Commented Oct 15, 2022 at 10:15
  • 2
    Greyed out means that the operator wasn't even executed. If this is a live query plan then maybe it hadn't even got to that part of the plan yet as another part was taking a long time to execute Commented Oct 15, 2022 at 10:53


Your Answer

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

Browse other questions tagged or ask your own question.