Bad Plan after a couple of days

Asked Jun 18 '15 at 18:55

Active Jun 19 '15 at 10:54

Viewed 111 times

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

Good Plan

asked Jun 18 '15 at 18:55

SqlSandwiches

1,607
1
15
20

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. – SqlSandwiches Jun 18 '15 at 19:07
1

Cant you rebuild stats more often? I excpect its your stats that are causing this. – James Anderson Jun 19 '15 at 09:01

Bad Plan after a couple of days

0 Answers0