2

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?

  • To just clarify, is that forty clients or forty thousand clients? – Shawn Melton Aug 27 '14 at 3:31
  • You can use option (recompile) as query hint or just recompile only that sp using sp_recompile – Kin Shah Aug 27 '14 at 12:19
  • If it were a cache query plan wouldn't you see a general performance issue after the first query rather then a gradual performance loss. Assuming that the parameter sniffing is only utilized for parameter code conditions. What about updating table stats? Would this also be a side effect from changing the compatibility level? – Steve Apr 3 '15 at 1:19
3

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.

1

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

0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.