0

I have a stored procedure that was working really well for about 3 weeks, than all of a sudden it basically ground to a complete Stop.

This was i believe due to the server creating an additional query plan that was far less efficient.

good plan bad plan

For whatever reason both of these plans were flushed from the server.

Is there a way to get the Server to recreate the good plan ?

i have a copy of the plan (the graphical format), the query hash and the plan hash but wasn't sure if the plan hash still exists anywhere after being flushed.

"Good" Plan: https://www.brentozar.com/pastetheplan/?id=S1TLgATDo

"Bad" Plan: https://www.brentozar.com/pastetheplan/?id=Hy5cJATwi

Improve this question
7
  • Which version of SQL Server are you using? Are you running a statistics / index maintenance? It might also be possible to fix the sproc so that it should provide a good plan. If you can get the execution plans for the slow and fast queries, paste links to those for analysis.
    – vonPryz
    Dec 7, 2022 at 8:02
  • Hi, we are on 2016. Do you want to see the 2 Query plans in their graphical format ? i can add them as images to my question if that would help.
    – Praxiom
    Dec 7, 2022 at 8:15
  • Is Query Store enabled on the database? If so, you could force the good plan.
    – Denis Rubashkin
    Dec 7, 2022 at 8:22
  • @DenisRubashkin it is not enabled and both plans were flushed from the server. i will enable it though as it looks helpful. Does it have much overhead?
    – Praxiom
    Dec 7, 2022 at 8:23
  • 1
    Unfortunately these are the estimated execution plans, which don't really tell so much as opposed to the actual execution plans. I'm assuming you don't have the actual execution plan for the good one, but please run your query with the actual execution plan enabled so you can get a copy of the actual plan for the bad one, and add it to your post. Also, both estimated plans appear very similar in shape, and the "good" one doesn't seem too great as it scans half a million and a quarter million rows for two of the index operations it's doing. Please also add your table / index definitions.
    – J.D.
    Dec 7, 2022 at 13:49

0

Reset to default

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.

Browse other questions tagged or ask your own question.