Sql-server – Procedure Cache Dropping

cachesql server

We've experienced an issue with SQL Server dropping proc cache out of the blue.

I'm talking 4GB down to 0 in 2 minutes. This has occurred approx once per day in the last two weeks.

It does happens at random times and does not coincide with process.

Has anyone seen this before?

So far as I'm aware the cache its only wiped if DBCC FREEPROCCACHE is executed or SQL is restarted.

Version: Microsoft SQL Server 2005 – 9.00.4226.00 (X64) May 26 2009
14:58:11 Copyright (c) 1988-2005 Microsoft Corporation Enterprise
Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

32GB RAM

Any help much appreciated.

We use Idera SQL Diagnostic manager.

Results:

DateTime                Proc Cache Size MB 
23/12/2015 19:19:00     4,165.63 
23/12/2015 19:25:00     4,165.71 
23/12/2015 19:32:00     4,178.93 
23/12/2015 19:38:00     4,175.44 
23/12/2015 19:44:00     4,176.87 
23/12/2015 19:50:00     4,179.80 
23/12/2015 19:57:00     207.16 
23/12/2015 20:03:00     459.66 
23/12/2015 20:09:00     510.48 
23/12/2015 20:15:00     1,795.31 
23/12/2015 20:21:00     2,830.94 
23/12/2015 20:27:00     2,859.87 
23/12/2015 20:34:00     2,877.93 
23/12/2015 20:40:00     2,891.94 
23/12/2015 20:46:00     2,908.82 
23/12/2015 20:52:00     2,921.34 
23/12/2015 20:58:00     2,975.92

Best Answer

One reason could be somebody changing options or running sp_configure. That would be logged in your error log.

Please read this article: Using Sp_configure To Change a Value Will Issue DBCC FREEPROCCACHE

using sp_configure to change a configuration value causes SQL Server to issue a DBCC FREEPROCCACHE statement.

and

The same behavior will occur if you use a GUI such as SQL Server Enterprise Manager that issues an sp_configure call that changes a parameter.

The Plan Caching and Recompilation in SQL Server 2012 white paper gives some other possibilities.

Flush Entire Plan Cache The following operations flush the entire plan cache, and therefore, cause fresh compilations of batches that are submitted the first time afterwards:

  • Detaching a database
  • Upgrading a database to a later database compatibility level
  • Restoring a database
  • DBCC FREEPROCCACHE command
  • RECONFIGURE command (many of the options to this command cause a flush)
  • ALTER DATABASE … MODIFY FILEGROUP command
  • Modifying a collation using ALTER DATABASE … COLLATE command

Flush Database-Related Entries in Plan Cache The following operations flush the plan cache entries that refer to a particular database, and cause fresh compilations afterwards.

  • DBCC FLUSHPROCINDB command
  • ALTER DATABASE … MODIFY NAME = command
  • ALTER DATABASE … SET ONLINE command
  • ALTER DATABASE … SET OFFLINE command
  • ALTER DATABASE … SET EMERGENCY command
  • DROP DATABASE command
  • When a database auto-closes

Lots of these events should be in your log files.

Related Question