My understanding is that some of the DMV's in SQL Server depend on query plans being cached. My questions are these. Are all query plans cached? If not, when is a query plan not cached? For ones that are cached, how long do they stay in the cache?
Thanks very much
Some of the SQL Server DMV's that capture tokens relating directly to the query plan cache, are at the mercy of the memory pressure placed on the query plan cache (due to adhoc queries, other memory usage and high activity, or through recompilation). The query plan cache is subject to plan aging (e.g. a plan with a cost of 10 that has been referenced 5 times has an "age" value of 50):
If the following criteria are met, the plan is removed from memory:
· More memory is required by the system
· The "age" of the plan has reached zero
· The plan isn't currently being referenced by an existing connection Ref.
Those DMV's not directly relating to the query plan cache are flushed under 'general' memory pressure (cached data pages) or if the sql server service is restarted.
The factors affecting query plan caching have changed slightly since SQL Server 2000. The up-to-date reference for SQL Server 2008 is here: Plan Caching in SQL Server 2008
I just want to add some geek minutia: The Query plan cache leverages the general caching mechanism of SQL Server. These caches use the Clock algorithm for eviction, see Q and A: Clock Hands - what are they for. For query plan caches, the cost of the entry takes into consideration the time, IO and memory needed to create the cache entry.
For ones that are cached, how long do they stay in the cache?
A valid object stays in cache until the clock hand decrements the cost to 0. See sys.dm_os_memory_cache_clock_hands. There is no absolute time answer to this question, the clock hand could decrement an entry to 0 in a second, in a hour, in a week or in a year. It all depends on the initial cost of the entry (query/schema complexity), on the frequency of reusing the plan, and the clock hands speed (memory pressure).
Cached object may be invalidated though. The various reasons why a Query plan gets invalidated are explained in great detail the white paper linked by Mitch: Plan Caching in SQL Server 2008.