What keeps clearing my SQL Server plan cache?

Question

We've had some performance issues recently so I've been monitoring our activity more closely. I've been running the AskBrent scripts and get reports "someone ran DBCC FREEPROCCACHE" because the plan cache was very young. Delving further using dm_exec_query_stats I see the plan cache keeps emptying as frequently as every few seconds. I've run a trace on the server and nothing is running any DBCC commands during the same period.

Anyone know what else could cause the plan cache to empty so frequently?

The database supports a number of busy websites in production. In our staging environment (running the same code) the plan cache is days old. In our dev environment the cache is between a few minutes or a few hours old, which isn't as surprising. We're running SQL Server 2008 R2 Standard in a cluster.

Answer 1

@Raspin: One possibility is that the server is memory constrained, and there are lots of "distinct" SQL plans (executions of queries that aren't parameterized or that can't be auto-parameterized).

It may be that there are so many "new" plans and that SQL server is flushing "old" plans. (There are operations other than a DBCC FREEPROCCACHE that cause plans to be flushed. If there is memory pressure, then Adhoc plans that aren't reused will be the first plans evicted from the cache.)

I recommend this Microsoft whitepaper for a relevant discussion:

http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

I'm not sure how a "cluster" affects the plan cache; I believe each SQL Server instance has it's own plan cache.

---

The statements that can't be "auto-parameterized" are listed at the end of the whitepaper in Appendix A of the white paper.

My guess (just a guess) is that the volume of "distinct" SQL statements overwhelms the plan cache. I'd venture that a lot of the statements don't ever get re-used.

On my server, I'd use dm_exec_cached_plans to collect a few snapshots of the plans in the cache [http://technet.microsoft.com/en-us/library/ms187404(v=sql.105).aspx] over a short interval, and compare the snapshots.

Does the number of entries stay relatively constant, is the cache full, or does it really get "cleared" at some point. Are any statements in the plan cache being reused? (I think the output includes a "usecounts" column that indicates that a statement is being re-executed.)

I'd also collect a trace of the SQL statements being executed over a short interval, and review those. Are most of the statements parameterized or auto-parameterized? Or are most of them singletons with literals.

SELECT *
  FROM sys.dm_exec_cached_plans
 WHERE cacheobjtype = 'Compiled Plan'
 ORDER BY objtype

Are most of the plans objtype = 'Adhoc'? Those are the most likely to be evicted when there's memory pressure, if the plans don't get reused.

---

Is the performance issue really that plans are being "cleared" out of the cache, or is it really something else, like the sheer volume of SQL statement,s the performance of the SQL statements themselves, contention for locks, latches, free buffers, etc.

---