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 (
select
reused = case when usecounts > 1 then 'reused_plan_mb' else 'not_reused_plan_mb' end,
size_in_bytes,
cacheobjtype,
objtype
from
sys.dm_exec_cached_plans
), cte2 as
(
select
reused,
objtype,
cacheobjtype,
size_in_mb = sum(size_in_bytes / 1024. / 1024.)
from
cte
group by
reused, cacheobjtype, objtype
), cte3 as
(
select
*
from
cte2 c
pivot
( sum(size_in_mb) for reused in ([reused_plan_mb], [not_reused_plan_mb])) p
)
select
objtype, cacheobjtype, [reused_plan_mb] = sum([reused_plan_mb]), [not_reused_plan_mb] = sum([not_reused_plan_mb])
from
cte3
group by
objtype, cacheobjtype
with rollup
having
(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.
PS
I’ve also updated my sys2dmv project on CodePlex with this new script:
http://sys2dmvs.codeplex.com/