6

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
Improve this question
9
  • @Hpk Is there anything in the error log when that happens? Things like configuration options changed or freeproccache are logged
    – Tom V
    Dec 18, 2015 at 12:50
  • 2
    What is the max memory and RAM on the server ? The plans will get flushed out if there is memory pressure.
    – Kin Shah
    Dec 18, 2015 at 12:59
  • 1
    this isn't a virtual server is it? doesn't look like it based on version output. if it is virtual, check if balloon driver is reclaiming memory at this time.
    – Bob Klimes
    Dec 23, 2015 at 20:21
  • Hi - nope its a physical server
    – Hpk
    Dec 23, 2015 at 20:33
  • 1
    does buffer cache also drop at the same time?
    – Bob Klimes
    Dec 24, 2015 at 15:32

1 Answer 1

Reset to default
6

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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