What keeps clearing my SQL Server plan cache?
@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.