These two behave exactly the same way,

• Ad hoc statements

> possibly auto parameterized statements.

• Dynamic String Execution (DSE)

> EXECUTE (@string)

• sp_executesql (force statement caching) - behaves like stored procedure.

• Prepared queries (forced statement caching though parameter markers).

> Client side from ODBC & OLEDB

> SQLPrepare, SQLExecute and iCommandPrepare

• Plan cache pollution is created by "single-use plans" executing and being stored but you might not really benefit.

• Each plan takes a multiple 8KB. (Each query statement = 8KB)

• The "query_class" is harder to track because each is listed in the cache, but they will have the same query_hash but different query_plan_hash 

• SQL 2008 (query_hash & query_plan_hash is in sys.dm_exec_query_stats)

> Aggregate by query_hash to find similar queries.

> Aggregate by query_hash & query_plan_hash to find similar queries together with their plan.

• Finding and Tuning Similar Query Reference:

• CACHESTORE_OBJCP = "Object Plans"

> Stored procedure, function, triggers ...

> Generally desirable

• CACHESTORE_SQLCP = "SQL Plans"

> Adhoc SQL Statements (including parameterized)

> Prepared statements

> OK when highly reused but often not

• CACHESTORE_PHDR = "Bound Trees"

> Views, constraints and defaults

> Usually OK

• CACHESTORE_XPROC = Extended  Procs

• Plan Cache (a.k.a. "procedure cache" 

• Uses stolen pages from the buffer pool (data pages)

• View cached plans: sys.dm_exec_cached_plans

• Memory limits:

> SQL 2008+ and SQL 2005 SP2

• 75% of visible target memory from 0-4GB

• +10% of visible target memory 4GB-64GB

• +5% of visible target memory > 64GB

> SQL 2005 RTM and SQL 2005 SP1

• 75% of visible target memory from 0-8GB

• +50% of visible target memory 8GB-64GB

• +25% of visible target memory > 64GB

> SQL 2000

• 4Gb upper cap on the plan cache

• Reusing plan cache can be good:

> When different parameters do not change the optimal plan, then saving and reusing is excellent.

> SQL Server saves time in compilation.

• Reusing plan can be VERY bad:

> When different parameters wildly change the size of the result set and the optimal plans vary, then reusing the plan can be horribly bad.

> If statistics are added to base tables, existing plans may not leverage them.

• When the plan for a given statement within a procedure is not consistent in execution plan, due to parameter changes.

• Cost of recompilation might be significantly less than the execution cost of bad plan.

Why?

• A much faster execution with a better plan.

• Some plans just don't work for a wide variety of your execution cases, some plans should never be saved.

Do we need to this in every procedure?

• NO, start with the expensive/highest priority procedures that aren't performing well - TEST before applying in PROD!

• CREATE _WITH RECOMPILE = NOT recommended

• EXECUTE _ WITH RECOMPILE = Use for testing

• sp_recompile objname = Just to re-invalidate

Statement-level recompilation

• 2005+ OPTION (RECOMPILE)

• 2008+ OPTION (OPTIMIZE FOR UNKNOWN)

Note: This is a short term fix - You must dig deeper in fixing query bottleneck.

• Stored procedure and sp_executesql have the same potential for executing a bad plan BUT stored procedure have more options for centralized control.

• Forcing a recompile can be warranted/justified.

• Always recompile the smallest amount possible.

• Reasons to recompile. See Pinal Dave post.