Plan Cache Pollution

Sql Server will cache plans for "safe" statements but not in the way you might expect. For example, if we pass different integer values for a parameter in a safe statement, Sql Server may cache tinyint, smallint, int and bigint plans, thereby leading to Plan Cache pollution.

If Sql Server deems a set of ad hoc identical statements to be unsafe, then it will not parameterize them and end up creating a separate plan for each of the statement. For instance WHERE predicates with = and LIKE may throw up different plans, depending on the data selectivity. So Sql Server may use a non-clustered index that is available or decide to go for a full table scan, even for the same LIKE - depending on what parameter value has been used.

Now compiling the identical queries each time and having the separate plans may be good too as it will be optimized for each statement. The downside is the plan bloat and non-reuse of already compiled plans.

Likewise, when using variables as the predicate parameters, SQL Server deems them to be unsafe, so even if we pass in a highly selective value for the parameter, the optimizer will not use the non- clustered index available and will treat the variable value as "Unknown", and choose an index scan plan. This is because the variable value is not known until runtime to Sql Server.

So using a variable in an sd hoc sql statement fundamentally changes the way Sql Server uses the optimizer!

--------------------------------------------------------------------------------------
Plan Cache pollution with Dynamic String Execution

Even though it is a safe statement, with string concatenation and no strongly type parameter, this will lead to plan cache pollution.

declare @sql nvarchar(4000), @memberId INT = 12345;
select @sql = N'Select * from member where memberId="
                           + CONVERT(nvarchar(10), @memberId);

Exec (@sql);

To make it strongly typed, convert the INT variable back to its data type after the string concatenation within the string like so:

declare @sql nvarchar(4000), @memberId INT = 12345;
select @sql = N'Select * from member where memberId= CONVERT(INT,'
                           + CONVERT(nvarchar(10), @memberId) + N')';

Exec (@sql);

This will result in just one plan, no matter how many times you execute it with different values for the INT parameter. So this is a good way to right dynamic sql with strongly typed parameters.