Query slows down after a couple of days, Why?

SQLSandwiches 2015-06-18 18:22:11

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?

2_3 is the old plan
3_3 is the new plan that works fine.

SQLHammer 2015-06-18 19:47:56
This is a common problem when the sets of data that you are working with in the stored procedure can be very different in size when parameters are changed. Glancing at your SQL text, the datetime ranges are likely offenders here.

First, the problem:

The problem is that your stored procedure will cache an execution plan which is optimized for a certain data skew. Then, when your parameters change and the data set is much different, your stored procedure will execute with the cached plan and perform very poorly.

The reason that ARITHABORT settings modify your experience in SSMS is because the set options are included in how SQL Server identifies whether the same query is being executed and can look into the plan cache for a pre-compiled plan. By default SSMS has ARITHABORT ON, while .NET, ODBC and OLEDB default to OFF. This difference means that, by default, SSMS will use a different compiled plan than your applications will.

There is a nice table here, in "The Default Settings" section.

Now for solutions:

  1. You can set WITH RECOMPILE in your stored procedure so that it always has the best plan possible. This has a performance and server resource hit to consider.
  2. A better option is to prevent parameter sniffing with the use of local variables. There is a lot of good information here.
  3. My preference, if you are using SQL Server 2008 or higher, is to use the OPTIMIZE FOR UNKNOWN query option. There is good information on that here.