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?