Sql Plan Cache

Running ad hoc statements (both Sql Server deemed safe and unsafe statements) pollute the plan cache. Running the same statements using sp_ExecuteSql (strongly typed and parameterized) helps avoid plan cache pollution for safe statements. But with unsafe statements, the forced plan caching by sp_ExecuteSql causes inaccurate estimates and related issues.
-------------------------------------------------------------------------------------------
Unlike adhoc statements that will create separate plans for different data types even for the same safe statement, sp_ExecuteSql will force one plan using strongly type parameterization. Consider the 2 batches below using sp_executesql(safe statement in this case):

declare @sql nvarchar(4000)
select @sql = N'Select n.* from member n where n.memberId = @memberId';
EXEC [sp_Executesql] @sql, N'@memberId INT', 123;

declare @sql nvarchar(4000)
select @sql = N'Select n.* from member n where n.memberId = @memberId';
EXEC [sp_Executesql] @sql, N'@memberId INT', 123456789;

Here a single plan will be in the plan cache and the same plan will be used in both the batch executions. So better plan reuse here. The plan like this is very stable and very beneficial to reuse the plan across multiple executions.

The catch is for unsafe or unstable plans, reusing the same plan forced by sql_executesql can actually be detrimental to sql performance!
------------------------------------------------------------------------------------------
From Pinal Dave

http://blog.sqlauthority.com/2010/08/28/sql-server-plan-cache-retrieve-and-remove-a-simple-script/

Running adhoc queries from say SSMS against Production would just pollute the plan cache and can cause cache bloat.
This query captures plans used only once in its lifetime

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;


You can see how much memory is already bloated by not-so-useful queries. If you want to remove any large plan cache which you do not think is useful to you, you can run the following command to remove it:

DBCC FREEPROCCACHE(plan_handle)
------------------------------------------------------------

From http://sqlmag.com/database-performance-tuning/sql-server-plan-cache-junk-drawer-your-queries

Script to find inefficient plans from the cache

--Missing Indexes
;WITH XMLNAMESPACES(DEFAULT
N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT dec.usecounts, dec.refcounts, dec.objtype
  , dec.cacheobjtype, des.dbid, des.text,deq.query_plan
FROM sys.dm_exec_cached_plans AS dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
WHERE
deq.query_plan.exist
(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple
/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY dec.usecounts DESC

--Convert Issues
;WITH XMLNAMESPACES(DEFAULT
N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
cp.query_hash, cp.query_plan_hash,
ConvertIssue =
  operators.value('@ConvertIssue','nvarchar(250)'),
Expression =
  operators.value('@Expression','nvarchar(250)'),
  qp.query_plan
FROM sys.dm_exec_query_stats cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY query_plan.nodes('//Warnings/PlanAffectingConvert')
  rel(operators)