Sample Header Ad - 728x90

Plan changes to include Eager Spool causes the query to run slower

0 votes
1 answer
1564 views
We have a reporting query which is erratic in terms of execution plan and run duration. It is either 5 seconds or as slow as 5 minutes. The query is a Select statement with no DML involved. One thing I noticed is the costly Eager Spool operator after an index scan in slow executions. However, index seek is used when executing fast. I updated the stats but it didn't help to speed up. I was just wondering why sometimes optimiser chooses to use Index Scan (and Eager Spool) rather than Index Seek and what causes it to do so? Attaching SS of both slow and fast execution plans. SS is saved as a file from Plan Explorer so may upload some other tabs if need be. Thank you Edit: The query runs fast in the mornings, slow in the afternoons, always. Fast execution Slow execution
Asked by Stackoverflowuser (1550 rep)
Apr 12, 2018, 12:17 PM
Last activity: Apr 13, 2018, 11:44 AM