Slow query performance
I am trying to invesitage slow run of query , as per execution 1 in plan step 8 and9 number of execs is 99743 whereas same query in execution 2 has got only 133 execs in step 8 and 9. Just wondering why execs value is so high in case of execution 1 (i was expecting it to be 133 as in step 4 we have only 133 rows in both executions).
Execution 1:
=============
SQL Plan Monitoring Details (Plan Hash Value=25004206)
=====================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) |
=====================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 3404 | +716 | 1 | 2 | | | | |
| 1 | HASH UNIQUE | | 246 | 8832 | 3404 | +716 | 1 | 2 | | | 444K | |
| 2 | NESTED LOOPS | | | | 4116 | +4 | 1 | 957K | | | | 0.02 |
| 3 | NESTED LOOPS | | 3335 | 8830 | 4116 | +4 | 1 | 127M | | | | |