0

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

Improve this question
6
  • How many processors / cores do you have and what are your max_degree_of_parallelism and cost threshold for parallelism settings for the instance?
    – John K. N.
    Commented Apr 12, 2018 at 12:22
  • 1
    Are you using exec ... with recompile for testing, or is this how you always call the procedure? Please see this great post by Paul White.
    – Aaron Bertrand
    Commented Apr 12, 2018 at 12:22
  • "with recompile" option was for testing and it didn't help.
    – Stackoverflowuser
    Commented Apr 12, 2018 at 12:24
  • 1
    Instead of images, upload your actual plan to brentozar.com/pastetheplan and add the link to your question.
    – Dan Guzman
    Commented Apr 12, 2018 at 12:25
  • 1
    ...and could you possibly post the whole SQL statement?
    – John K. N.
    Commented Apr 12, 2018 at 12:29

1 Answer 1

Reset to default
3

Your queries are using totally different indexes, which means the optimizer is choosing different plans based on different parameters.

You're able to see the difference with the recompile hint (which means parameter sniffing is involved), which is good. How you fix it is up to you.

In the fast plan, these indexes get used:

NUTS

Object 13: Index 2, Object 12: Index 3, Object 7: Index 4.

In the slow plan, these get used.

NUTS

Object 13: Index 3, Object 12: Index 4, Object 7: Index 5.

Since the plan is anonymized, and you've only posted pictures of it, what you need to do is look at which parameters were used in each execution, and try out using the OPTIMIZE FOR (please for the love of Joe Sack don't use UNKNOWN) hint to see if the 'fast' plan is still fast for the set of parameters used to get the slow plan.

It may not be, which is totally possible. From there, you'd need to look at the query and indexes to tune things so you have a better general plan. It'll be really hard to help if you keep posting anonymized stuff, though.

Improve this answer
3
  • Thank you. I may outright say that the set of parameters doesn't effect the duration. However, I observed that the time of the day is a factor i.e always slow running in the mornings, fast in the afternoons.
    – Stackoverflowuser
    Commented Apr 12, 2018 at 13:18
  • 1
    @Stackoverflowuser SQL Server doesn't have periods where it decides to work less hard. I don't think you're giving the parameter aspect of this enough thought.
    – Aaron Bertrand
    Commented Apr 12, 2018 at 17:21
  • @AaronBertrand I thought it might be due to some maintenance like statistics update\index defrag etc. happening after slow executions or maybe bulk inserts\deletes taking place sometime...Just wanted to include as much info as possible regarding the problem to give insight into the issue. I witnessed in multiple occasions the same set of parameters ran slowly in the morning fast in the afternoon.
    – Stackoverflowuser
    Commented Apr 13, 2018 at 6:49

Your Answer

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

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