Simple parameterization of pain in SQL Server

In today's article, I'd like to talk about some of the features and side effects of simple parameterization (simplicity Parameterization) used by SQL Server for Ad hoc SQL statements (Ad-hoc SQL statements). First, if your SQL statement contains these, simple parameterization does not occur:

• join
• in
• bulk INSERT
• union
• into
• distinct
• top
• group by
• having
• compute
• sub Queries

In general, if you are dealing with the so-called Security Execution Plan (safe execution plans), SQL Server automatically parameterize your SQL statement: Regardless of the supplied parameter value, the query must always lead to the same execution plan. If you have a bookmark search in your execution plan, this is an example of an impossibility. Because the critical point defines whether to make a bookmark lookup or a full-table/clustered Index scan.

automatic parameterization is not so cool!

If SQL Server can automatically parameterize your SQL statements, you still have to consider some of the side effects of the automatic parameterized SQL statements introduced by SQL Server. Let's look at a concrete example. The following query creates a table that executes a simple SQL statement that is automatically parameterized by SQL Server.

1 --Create a simple table2 CREATE TABLEOrders3 (4Col1INT IDENTITY(1,1)PRIMARY KEY  not NULL,5PriceDECIMAL( -,2)6 )7 GO8 9 --This query gets auto parametrized, because it's a simple query with a safe (consistent) planTen SELECT *  fromOrders One WHEREPrice= 5.70 A GO -  - --Analyze the Plan Cache the SELECT -St.text,  - Qs.execution_count, - Cp.cacheobjtype, + Cp.objtype, -Cp.*, +Qs.*,  AP.*  at  fromSys.dm_exec_cached_plans CP -  CrossAPPLY sys.dm_exec_query_plan (cp.plan_handle) p -  CrossAPPLY sys.dm_exec_sql_text (cp.plan_handle) St -  Left JOINSys.dm_exec_query_stats QS onQs.plan_handle=Cp.plan_handle - WHERESt.text  like '%orders%' - GO

Then when you look at the plan cache, you'll see that SQL Server can automatically parameterize SQL statements for you:

(@1 numeric (3,2)) SELECT * FROM [Orders] WHERE [price][email protected]

But what is the selected data type as a parameter? The smallest possible one! Here is numeric (3,2)! If you now perform the following 2 queries:

1 --Execute a slightly different query2 SELECT *  fromOrders3 WHEREPrice= 8.704 GO5 6 --Execute a slightly different query7 SELECT *  fromOrders8 WHEREPrice= 124.509 GO

SQL Server can reuse the execution plan for a parameterized SQL statement that uses a 8.7-value SQL statement for the 1th. But what about a 2nd SQL statement with a value of 124.50? The plan for this SQL statement cache cannot be reused because the 124.50 value does not conform to numeric (3,2). In this case, SQL Server generates a new parameterized version of your SQL statement with the numeric (5,2) data type. You just used the extra parameterized version of your SQL statement to contaminate your plan cache! It gets worse when you execute the following statement:

-- Execute a slightly different query SELECT *  from Orders WHERE = 1204.50 GO

This will give you a new one again. New parameterized version with numeric (6,2) data type-another version in the plan cache! When I show this behavior, many people suggest that I should use reverse order to execute the SQL statement just now. Let's try it out by emptying the plan cache first.

1 --Clear the Plan Cache2 DBCCFreeproccache3 GO4 5 --Execute a slightly different query6 SELECT *  fromOrders7 WHEREPrice= 1204.508 GO9 Ten --Execute a slightly different query One SELECT *  fromOrders A WHEREPrice= 124.50 - GO -  the --Execute a slightly different query - SELECT *  fromOrders - WHEREPrice= 8.70 - GO

And then when you look at the plan cache, nothing changes: SQL Server also generates 3 different parameterized SQL statements-each with the smallest possible data type.

It doesn't matter what you do, that is, the order in which you execute your SQL statements: During automatic parameterization, SQL Server always chooses the smallest possible data type. When you rely on the SQL Server feature, think about it.

What about varchar? When SQL Server automatically parameterize SQL statements that contain character values (such as varchar), things get better. Suppose you have the following table definitions and the following 2 queries:

1 --Create Another table to demonstrate this problem2 CREATE TABLEOrders33 (4Col1INT IDENTITY(1,1)PRIMARY KEY  not NULL,5Col2VARCHAR( -)6 )7 GO8 9 --clears the Plan CacheTen DBCCFreeproccache One GO A  - --a varchar/char column is always auto parametrized to A VARCHAR (8000) - SELECT *  fromOrders3 the WHERECol2= 'Woody' - GO -  - --a varchar column is all auto parametrized to A varchar (8000) + SELECT *  fromOrders3 - WHERECol2= 'Tu' + GO

In this case, SQL Server generates 1 automatic parameterized SQL statements with varchar (8000)-the largest possible data type. From the example just now, this is the behavior you expect. Sometimes SQL Server good bad things do at the same time ...

Summary

Automatic parameterization can be great when you're dealing with simple SQL statements. But as you can see in this article, you need to know the side effects that SQL Server introduces. In addition, the simple parameterization of SQL Server will provide you with the mandatory parameterization (forced Parameterization) feature, which I will introduce in a later article.

Thanks for your attention!

Simple parameterization of pain in SQL Server