I have this query that runs fine for a couple of days (from the application) and then slows down to where it can't even finish.

If I rebuild the plan, it flies.

There is a signifigant amount of data being modified in the tables that it is using.

Also if I run it from SSMS and turn ARITHABORT on, it works fine. If I turn ARITHABORT off, it hangs and hangs.

Side note - Stats are repopulated every Monday. Today (Thursday) the plan is bad. The table in question has 1.4 billion records with 9 million changed since Monday.

Is there a way I can increase performance so I don't have to rebuild this plan?

Bad Plan Bad Plan

Good Plan Good Plan

  • 1
    Have you looked at the compiled parameters for each plan? My guess is they're probably different. If so, you could use OPTIMIZE FOR (good parameter)
    – Queue Mann
    Jun 18, 2015 at 19:00
  • I have. They are the same. Jun 18, 2015 at 19:07
  • 1
    Cant you rebuild stats more often? I excpect its your stats that are causing this. Jun 19, 2015 at 9:01


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.