Source: Forced and Simple Parameterization in SQL Server 2005 (TSQLNet Weblog - Cihangir Biyikoglu)
SELECT
c.[text],
a.refcounts,
a.usecounts,
d.query_plan
FROM sys.dm_exec_cached_plans a
OUTER APPLY sys.dm_exec_plan_attributes(a.plan_handle) b
OUTER APPLY sys.dm_exec_sql_text(CAST(b.VALUE AS VARBINARY)) c
OUTER APPLY sys.dm_exec_query_plan(a.plan_handle) d
WHERE b.attribute='sql_handle' AND a.cacheobjtype = 'Compiled Plan'
ORDER BY a.usecounts DESC
Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro