Question about ad hoc Compiled Plans in syscacheobjects

When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks
<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegr oups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/de...qlquerproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David