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 '15 at 19:00
  • I have. They are the same. Jun 18 '15 at 19:07
  • 1
    Cant you rebuild stats more often? I excpect its your stats that are causing this. Jun 19 '15 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.