Changing compatibility level of database causes performance improvement of stored procedure

Question

We have a Web Application in a Windows Server 2012 Datacenter (IIS 8), running under .NET Framework 4.0, which connects to another Windows Server 2012 Datacenter with SQL Server 2012. My Database has a Stored Procedure that obtains 3 parameters UserID, StartDate and EndDate. This Stored Procedure runs very well but at the end of the month (when all the +40.000 clients uses it) starts to decrease its performance gradually.

The weird thing is that when we Changed the compatibility level to Sql Server 2008 It automatically "refreshed" and stared to work very fast, and then again gradually decreased its performance until we (again) took the compatibility lever back to 2005 and stared to work properly.

Why is it that changing the Compatibility Level the Main Stored Procedure improves a lot after decreasing its performance gradually?

Answer 1

This is because of the changes affected when you modify the compatibility level of any database within SQL Server. This was an affect seen starting at SQL Server 2008 I believe, at least it shows up in documentation since then.

As stated here on MSDN for the ALTER DATABASE SET COMPATIBILITY_LEVEL:

Compatibility Levels and Stored Procedures

When a stored procedure executes, it uses the current compatibility level of the database in which it is defined. When the compatibility setting of a database is changed, all of its stored procedures are automatically recompiled accordingly.

So you basically will see every query plan currently in cache get marked for recompile, this would include stored procedures as noted above. So your initial performance improvement is only being seen because the plan for that stored procedure was recompiled. Then after it ran for some time the plan was not sufficient.

Instead of going back and forth between compatibility levels you should try optimizing the procedure so the query plan generated is more optimal for the range of parameter values being passed. Quick alternative, if code changes cannot be done immediately, might be to just schedule a job during the heavy use to initially mark the procedure for recompile.

Answer 2

In addition to Shawn Melton, because I can not comment yet, you can update your statistics regularly. This will generate a new execution plan. After that recompile your stored procedure with sp_recompile.

Here is a link for a great script: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Answer 3

Looks like a case of Parameter Sniffing!

Check google on it.

Running these commands will have the same effect:

DBCC FREEPROCCACHE

This command removes all of the cached query plans and execution contexts from the plan cache. It is not advisable to run this command on a production server because it can adversely affect performance of running applications. This command is useful to control plan cache's contents when troubleshooting a recompilation issue.

DBCC FLUSHPROCINDB( db_id )

This command removes all of the cached plans from the plan cache for a particular database. It is not advisable to run this command on a production server because it can adversely affect performance of running applications.

DBCC FREESYSTEMCACHE(cache[,resource pool])

This command removes all the plans in a particular cache. The value ‘ALL’ can also be provided for the cache. Optionally in SQL Server 2008, the effect of this command can be limited by a Resource Governor resource pool name. This latter option could be useful for cleaning up ad-hoc query plans associated with a specific resource pool, when the ‘Sys Plans’ cache option is also provided. It is not advisable to run this command on a production server unless its effect is fully understood as it can adversely affect performance of running applications.

This is a work around not a solution to the problem!