Forced parameterization

Question

What reasons would you not choose to force parameterization over simple parameterization for special queries in SQL Server?

Is there any performance overhead? If it would not be offset by (possible) winnings resulting from the reuse of query plans?

+3

Brett Postin Apr 24 '12 at 12:19

1 answer

Aaron Bertrand · Accepted Answer · 2012-04-24T12:33:05+0000

Have you analyzed your plan cache to find out if you have a large number of one-time plans?

SELECT usecounts, COUNT(*), SUM(size_in_bytes) 
  FROM sys.dm_exec_cached_plans
  GROUP BY usecounts
  ORDER BY usecounts;

SQL, , . , , ( , , , ). , , DBCC FREEPROCCACHE , , . (, , " ad hoc" , , , , , ).

, , (. " " ).