I just finished reading an excellent CSS article, How Simple Parameterization works, which is well written and well worth a look. In short, it outlines a customer performance issue caused by a change in the query plan – from parameterized to unparameterized – and a few considerations on resolving it.
Concepts worth digging into that are mentioned in the article:
- Simple parameterization – “If a SQL statement is executed without parameters, SQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan.” (“Auto-parameterization” in SQL Server 2000.)
- Forced parameterization – This specifies “that all SELECT, INSERT, UPDATE, and DELETE statements in a database be parameterized, subject to certain limitations. Forced parameterization is enabled by setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement”
- XML plans – See also SET SHOWPLAN_XML.
- Plan guides – “…can be used to optimize the performance of queries when you cannot or do not want to change the text of the query directly. … Plan guides influence optimization of queries by attaching query hints or a fixed query plan to them.”
Thanks to Grant Fritchey (Twitter) for the link.
Happy days,
Jen McCown
MidnightDBA.com/Jen