Cached Plans are typically only removed from the plan cache under memory pressure.
SQL Server primarily considers the cost of the plan when deciding which plans to remove. Lost-cost plans are removed before high-cost plans. The "cost" here is not directly the same as the "plan cost" you see when looking at execution plans - it's a costing mechanism associated with the cache.
When SQL Server detects memory pressure, it removes zero-cost plans from the cache, then reduces the cost of the remaining plans by 50%. For ad-hoc plans, the cost of the plan is considered to be zero, however that cost is increased by one every time the plan is reused. If you have "optimize for ad-hoc plans" enabled, it's likely the dynamic queries you're seeing in the cache have been heavily used, and as a result have a "high" cache cost, and are not being evicted. For non-ad-hoc plans, the plan cost is not incremented each time a plan is used, but is kept at the original plan cost, which is based on the execution-plan cost. You can see the number of times a particular plan has been used in the sys.dm_exec_cached_plans DMV.
Cache cost is measured in units known as "ticks", with a maximum of 31. Ticks are incremented according to this:
- each I/O adds 1 tick, up to a maximum of 19 ticks.
- each context switch adds one tick, up to a maximum of 8 ticks.
- one tick per 16 pages of memory, with a maximum of 4 ticks.
Ticks start to be decremented once the plan cache reaches 50% of its capacity. At that point, SQL Server initiates a resource monitor thread that decrements each plan tick-count by 1 each time the cache is populated with another plan.
The sys.dm_os_memory_cache_entries DMV contains details about what objects are in the cache, along with the original and current costs, among a number of other useful metrics.
The details listed above are based on details found in SQL Server Internals by Kalen Delaney.
