One interesting observation that may help developers get convinced that they should parametrize query and that they must check that the ORM they use does it correctly is show how much memory can be wasted by plans that cannot be effectively reused.
The following query can help on this:
with cte as (
reused = case when usecounts > 1 then 'reused_plan_mb' else 'not_reused_plan_mb' end,
), cte2 as
size_in_mb = sum(size_in_bytes / 1024. / 1024.)
reused, cacheobjtype, objtype
), cte3 as
( sum(size_in_mb) for reused in ([reused_plan_mb], [not_reused_plan_mb])) p
objtype, cacheobjtype, [reused_plan_mb] = sum([reused_plan_mb]), [not_reused_plan_mb] = sum([not_reused_plan_mb])
(objtype is null and cacheobjtype is null) or (objtype is not null and cacheobjtype is not null)
The result is something like this:
as you can see this server (SQL Server 2005 SP2) is using near 1.8GB of memory for Plan Caching and one third is memory that contains plans that are never reused (column usecount = 1 as reported by DMV sys.dm_exec_cached_plans)
Monitoring the result of this query can also show how much impact the usage of the new (SQL Server 2008) “Optimize for ad-hoc workload” option can have on a system.
I’ve also updated my sys2dmv project on CodePlex with this new script: