To clear cached plans not used for a set time, list the plans by their last execution time or by the time they were built, then clear each one by its plan handle. This post builds a small procedure that previews the plans first and removes them only when you switch it on. It also explains why the plan handle needs its 0x prefix.
Frequently Run Stored Procedures: Find Them in the Cache
Frequently run stored procedures leave an execution count in the plan cache. A tested demo ranks three procedures by count, reads and calls per minute. It also shows what the cache forgets and when to use Query Store instead.
Trivial Plan: Why Some Queries Skip Full Optimization
Identify a trivial plan in SSMS and cached XML, understand skipped optimizer work, and compare a more complex query without forcing it.
Removing a Bad Plan From Cache Without Clearing Everything
Remove one bad plan from cache by handle, compare narrower recompilation choices, and fix the cause so the plan stays healthy.
CHECKPOINT vs DBCC DROPCLEANBUFFERS: Impact on Memory
CHECKPOINT vs DBCC DROPCLEANBUFFERS comes down to which pages leave memory. CHECKPOINT writes dirty pages to disk, and DBCC DROPCLEANBUFFERS removes clean pages from the buffer pool. Here is the effect on memory, with results.
Plan Cache Memory by Cache Store: Where Plans Use Your RAM
Break down plan cache memory by cache store and object type, compare memory clerks, and investigate unused plans without cache clearing.
Compiled Parameter Values in SQL Server: Read Them From the Cache
Compiled parameter values show which value SQL Server used when it built a cached plan. This post reads them from the plan cache with one query, then shows how the first caller shapes the plan for everyone after. It ends with the usual ways to break that link.