What is a clean buffer in DBCC DROPCLEANBUFFERS? No one had asked me this in 20 years, so here is the answer in the simplest words I could find.
Clear the Plan Cache for a Single Database in SQL Server
To clear the plan cache for one database, use ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE in that database. This post tests it on two small databases, compares it with DBCC FLUSHPROCINDB and with freeing a single plan, and explains why DBCC FREEPROCCACHE is a bad habit.
Find the Oldest Query Plan in the Plan Cache
To find the oldest query plan in the plan cache, sort sys.dm_exec_query_stats by creation_time. This post tests the idea on a demo with three procedures, compares the oldest plan with the server start time, and lists what clears the plan cache.
How to Find Queries Using an Index From SQL Server Plan Cache? – Interview Question of the Week #195
Before I dropped an unused index, my client’s CEO wanted proof. Here is how to find queries using an index from the SQL Server plan cache.
Clear Procedure Cache for One Database in SQL Server
To clear procedure cache for one database, run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE inside that database. This post tests it on two demo databases, shows that the other database keeps its plans, and shows how to remove a single plan by its plan handle. It also covers the cost and the lighter alternatives.
Optimize for Ad Hoc Workloads: When to Turn It On
Optimize for Ad Hoc Workloads shrinks the memory that one-time queries take in the plan cache. This post builds a demo of single-use plans, measures their share of the cache, and shows how to read the result. It also covers the cost of the setting and why parameterized queries are the better fix.
SQL SERVER – How to Clear Plan Cache with Database Scoped Configuration?
I can Clear Plan Cache for the current database through database-scoped configuration. I verify the context before considering eviction.