Introduction SQL Server caches execution plans for reuse. When an application generates ad-hoc queries (with literal values instead of parameters), each unique query text gets its own cached plan. This plan cache bloat consumes memory that should be used for the buffer pool, degrading overall database performance.

Symptoms - `CACHESTORE_SQLCP` consuming GBs of memory in `sys.dm_os_memory_clerks` - Buffer pool hit ratio dropping as plan cache displaces data pages - `sys.dm_exec_cached_plans` showing thousands of ad-hoc plans with `usecounts = 1` - Memory pressure errors despite having ample physical RAM - Query compilation time increasing due to cache size

Common Causes - Application building SQL strings with concatenated literal values - ORM generating non-parameterized queries - `OPTIMIZE FOR AD HOC WORKLOADS` not enabled - Lack of forced parameterization on the database - One-off reporting queries creating large plans

Step-by-Step Fix 1. **Check plan cache usage": ```sql -- Memory used by plan cache SELECT type, name, pages_kb / 1024 AS pages_mb FROM sys.dm_os_memory_clerks WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP') ORDER BY pages_mb DESC;

-- Ad-hoc plans with usecounts = 1 SELECT COUNT(*) AS single_use_plans, SUM(size_in_bytes) / 1024 / 1024 AS total_mb FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE objtype = 'Adhoc' AND usecounts = 1; ```

  1. 1.**Enable optimize for ad-hoc workloads":
  2. 2.```sql
  3. 3.EXEC sp_configure 'show advanced options', 1;
  4. 4.RECONFIGURE;
  5. 5.EXEC sp_configure 'optimize for ad hoc workloads', 1;
  6. 6.RECONFIGURE;

-- This stores a small plan stub for first-use ad-hoc queries -- and only compiles the full plan on second execution ```

  1. 1.**Enable forced parameterization":
  2. 2.```sql
  3. 3.ALTER DATABASE mydb SET PARAMETERIZATION FORCED;

-- This forces SQL Server to parameterize queries automatically -- SELECT * FROM orders WHERE id = 123 -- becomes SELECT * FROM orders WHERE id = @p1 ```

  1. 1.**Clear the plan cache if needed":
  2. 2.```sql
  3. 3.-- Clear entire plan cache (use with caution)
  4. 4.DBCC FREEPROCCACHE;

-- Clear cache for a specific database DECLARE @db_id INT = DB_ID('mydb'); DBCC FLUSHPROCINDB(@db_id);

-- Or remove specific plans DECLARE @plan_handle VARBINARY(64); SELECT TOP 1 @plan_handle = plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE text LIKE '%SELECT * FROM orders WHERE customer_id = %'; DBCC FREEPROCCACHE(@plan_handle); ```

  1. 1.**Fix the application to use parameterized queries":
  2. 2.```csharp
  3. 3.// BAD: String concatenation
  4. 4.// var cmd = new SqlCommand($"SELECT * FROM orders WHERE id = {orderId}", conn);

// GOOD: Parameterized query var cmd = new SqlCommand( "SELECT * FROM orders WHERE id = @orderId", conn); cmd.Parameters.AddWithValue("@orderId", orderId); ```

Prevention - Enable `OPTIMIZE FOR AD HOC WORKLOADS` on all production instances - Use forced parameterization for databases with ad-hoc workloads - Monitor `CACHESTORE_SQLCP` memory usage with alerting - Fix application code to use parameterized queries - Use stored procedures for complex queries - Review ORM-generated queries for parameterization - Set `max server memory` to leave room for plan cache growth