DaveWentzel.com            All Things Data

Parameterization

 

Parameterization can occur on the client-side when the query is sent to SQL Server, or on the database server itself.  On the client-side this means use parameter markers that send sp_executesql RPC's to SQL Server, it doesn't mean stringing together SELECT statements that will lead to sql injection attacks.
 
On the server-side simple parameterization (discussed below) occurs next.  You can also use Plan Guides (sp_create_plan_guide) to set, for instance, forced parameterization.  (Need examples here). 
 
OPTIMIZE FOR may also help here. 
 
Forced Parameterization
 
Can be enabled at the query level using a plan guide (discussed above), or at the server instance level.  The preferred method is at the query level.  Forced parameterization is used when simple parameterization is causing to many recompiles of your statements and it is affecting performance negatively.  BOL has more info on forced parameterization.  Forced parameterization can lead to other problems though, namely Parameter Sniffing
 
Auto Parameterization/Simple Parameterization
 
DBCC FREEPROCCACHE

GO
 
Use AdventureWorks
GO



SELECT *

FROM Sales.SalesOrderHeader

WHERE SalesOrderID = 56000

GO



select  stats.execution_count AS exec_count,

p.size_in_bytes as [size],

[sql].[text] as [plan_text]

from sys.dm_exec_cached_plans p

outer apply sys.dm_exec_sql_text (p.plan_handle) sql

join sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

GO



exec_count  size plan_text

---------- ----- ------------------------------------------------------------------------

         1 57344 (@1 int)SELECT * FROM [Sales].[SalesOrderHeader] WHERE [SalesOrderID]=@1
 
 
If you change the SalesOrderID to another number and execute you should see the exec_count increment.  This is called auto-parameterization or simple parameterization.  SQL Server handles this for you.  But even a slight change like inserting a comment or a tab will cause a different plan to be created.  Auto-parameterization is very simple, no TOP clause, DISTINCT, UNION, IN clause, JOIN, or pretty much anything else that isn't very basic. 
 
So suppose you do want to reuse a query plan that the optimizer thinks is a separate query...say it has a JOIN but only one WHERE condition.  Wrap the statement in sp_executesql and pass the parameter in.  This is what an RPC call from ADO looks like in Profiler. 
 
 

Add new comment