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?
option (recompile)as query hint or just recompile only that sp using