Changing compatibility level of database causes performance improvement of stored procedure 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?
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.