evils of dynamic SQL

What are the cons of dynamic SQL as it relates to performance ? Are any of
the cons improved upon in SQL 2005 ?
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eWy5Kt4IHHA.3312@.TK2MSFTNGP03.phx.gbl...
> What are the cons of dynamic SQL as it relates to performance ?
A SQL query with hard-coded parameters usually doesn't reuse an existing
query plan, and requires a compilation. Queries whose plans obviously don't
depend on their parameter values may be auto-parameterized by SQL Server
(think primary key columns). But many queries will get a new plan for each
invocation. For really expensive queries this is either OK since the query
plan compilation cost is small relative to the query execution cost, or
actually GOOD if the cost of a suboptimal plan exceeds the cost of an
optimal plan by more than the cost of the compilation.
Dynamic SQL queries usually have hard-coded parameters, and so they often
have unnecessarily low query plan reuse. For workloads with a large number
of queries/sec, this can cause a high number of recompiles (CPU) and
increase the size and decrease the effectiveness of the query plan cache
(Memory, CPU).
See:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

>Are any of the cons improved upon in SQL 2005 ?
Yes. Plan Guides, and Forced Parameterization.
Forcing Query Plans
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
Understanding Plan Guides
http://msdn2.microsoft.com/en-us/library/ms190417.aspx
Forced Parameterization
http://msdn2.microsoft.com/en-us/library/ms175037.aspx
David
|||You might find Erland's article useful.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eWy5Kt4IHHA.3312@.TK2MSFTNGP03.phx.gbl...
> What are the cons of dynamic SQL as it relates to performance ? Are any of
> the cons improved upon in SQL 2005 ?
>
>