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
Mike Zalansky
  • 250
  • 1
  • 10
Hpk
  • 304
  • 2
  • 12
  • @Hpk Is there anything in the error log when that happens? Things like configuration options changed or freeproccache are logged – Tom V Dec 18 '15 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 '15 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 '15 at 20:21
  • Hi - nope its a physical server – Hpk Dec 23 '15 at 20:33
  • Has anything changed on your box since this is a recent behavior? For instance, is there a new application forcing SQL Server to surrender memory? What monitoring tools do you have? – Mike Zalansky Dec 23 '15 at 22:06
  • Is "Lock Pages in Memory" enabled? – Hannah Vernon Dec 23 '15 at 23:08
  • Can you show us the message which says Cache is dropping ? Is this a NUMA system ? – Shanky Dec 24 '15 at 03:57
  • I know this suggestion is a band aid until the root issue is identified but, set you minimum server memory from 0 to a level that will retain your buffer pool and minimize the performance issues you are having with the buffer pool rebuilding every time this happens. If there is a non SQL Server application stealing your memory, the result of setting your min memory level could slow the startup of this application. – Mike Zalansky Dec 24 '15 at 14:38
  • 1
    does buffer cache also drop at the same time? – Bob Klimes Dec 24 '15 at 15:32

1 Answers1

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.

Tom V
  • 15,670
  • 7
  • 63
  • 86