Plan Cache Pollution

They say if you can't explain something easily, you don't understand it well enough. For that reason, I'm revisiting some things I take for granted.

The Plan Cache is where SQL Server stores exection plans.

If queries are not parameterized, the plan cache fills with lots of similar queries of hard coded values.

SELECT mycolumns FROM mytable WHERE myid = 1
SELECT mycolumns FROM mytable WHERE myid = 2
SELECT mycolumns FROM mytable WHERE myid = 3
SELECT mycolumns FROM mytable WHERE myid = 4

If queries are parameterized (or parameterization is forced on at a database level) then 1 entry will be in the cache instead.

SELECT mycolumns FROM mytable WHERE myid = n

Correctly parameterized queries will prevent wasting cpu (generating plans) & memory (storing them). Allowing the cache to fill with simiar plans in this way is Plan Cache Pollution.

You can examine the plan cache with the sys.dm_exec_cached_plans DMO.

Links -
Tamarick Hill : sys.dm_exec_cached_plans
Klaus Aschenbrenner : Plan Cache Pollution