In today’s blog posting I want to talk about some specifics and side-effects of the Simple Parameterization mechanism used by SQL Server for ad-hoc SQL statements. First of all, Simple Parameterization will not happen if your SQL statements contain any of these:

• JOIN
• IN
• BULK INSERT
• UNION
• INTO
• DISTINCT
• TOP
• GROUP BY
• HAVING
• COMPUTE
• Sub Queries

In general SQL Server only auto parameterizes your SQL statements if you deal with a so-called Safe Execution Plan: regardless of the provided input parameter values, the query must always lead to the same execution plan. This is for example not possible if you have Bookmark Lookups in your execution plans, because the Tipping Point defines if a Bookmark Lookup or if a complete Table Scan/Clustered Index Scan is performed.

Auto Parameterization is not that cool!

If SQL Server is able to auto parameterize your SQL statement, you still have to think about some side-effects that the auto parameterized SQL statement introduces in SQL Server. Let’s have a look at a concrete example. The following query creates a table, and executes a simple SQL statement that can be auto parameterized by SQL Server.

-- Create a simple table
CREATE TABLE Orders
(
	Col1 INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	Price DECIMAL(18, 2)
)
GO

-- This query gets auto parametrized, because it is a simple query with a safe (consistent) plan
SELECT * FROM Orders
WHERE Price = 5.70
GO

-- Analyze the Plan Cache
SELECT
	st.text, 
	qs.execution_count, 
	cp.cacheobjtype,
	cp.objtype,
	cp.*,
	qs.*, 
	p.* 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
LEFT JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE st.text LIKE '%Orders%'
GO

When you afterwards look into the Plan Cache, you can see that SQL Server was able to auto parameterize the SQL statement for you:

(@1 numeric(3,2))SELECT * FROM [Orders] WHERE [Price]=@1

But what was chosen as the data type of the parameter? The smallest possible one! In our case a NUMERIC(3, 2)! If you now execute the following 2 queries.

-- Execute a slightly different query
SELECT * FROM Orders
WHERE Price = 8.70
GO

-- Execute a slightly different query
SELECT * FROM Orders
WHERE Price = 124.50
GO

SQL Server is able to reuse the execution plan of the parameterized SQL statement for the first SQL statement with the value of 8.70. But what about the 2nd SQL statement with the value of 124.50? For this SQL statement the cached plan can’t be reused, because the value of 124.50 doesn’t fit into a NUMERIC(3, 2). In that case SQL Server generates a new parameterized version of your SQL statement with a data type of NUMERIC(5, 2). You just polluted your Plan Cache with an additional parameterized version of your SQL statement! It gets even worse when you execute the following statement:

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

This one gives you again a new parameterized version with a data type of NUMERIC(6, 2) – another version in the Plan Cache! When I show this specific behavior during my various SQL Server workshops, people always suggest to me afterwards that I should just execute the SQL statements in the opposite order. Let’s try that by first clearing the Plan Cache.

-- Clear the Plan Cache
DBCC FREEPROCCACHE
GO

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

-- Execute a slightly different query
SELECT * FROM Orders
WHERE Price = 124.50
GO

-- Execute a slightly different query
SELECT * FROM Orders
WHERE Price = 8.70
GO

If you afterwards look again into the Plan Cache, nothing has changed: SQL Server has still generated 3 different parameterized SQL statements – with the smallest possible data type every time!

It doesn’t matter how you do it, and in which order you execute your SQL statements: SQL Server will always choose the smallest possible data type during the Auto Parameterization. Just think about that when you rely on that feature in SQL Server.

And what about VARCHAR? When SQL Server auto parameterizes a SQL statement that contains a character value (like VARCHAR), things change for the better. Let’s assume the following table definition and the following 2 queries:

-- Create another table to demonstrate this problem
CREATE TABLE Orders3
(
	Col1 INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	Col2 VARCHAR(100)
)
GO

-- Clears the Plan Cache
DBCC FREEPROCCACHE
GO

-- A VARCHAR/CHAR column is always auto parametrized to a VARCHAR(8000)
SELECT * FROM Orders3
WHERE Col2 = 'Klaus'
GO

-- A VARCHAR column is always auto parametrized to a VARCHAR(8000)
SELECT * FROM Orders3
WHERE Col2 = 'Aschenbrenner'
GO

In that case, SQL Server just generates 1 auto parameterized SQL statement with a data type of VARCHAR(8000) – the largest possible data type. That’s the preferred behavior that you would also have expected from the previous examples. Sometimes SQL Server does good and bad things at the same time…

Summary

Auto Parameterization can be great when you are working with simple SQL statements. But as you have seen in this blog posting, you have to know the side-effects that are introduced by SQL Server. Besides the Simple Parameterization feature SQL Server also offers you the Forced Parameterization functionality about which I will talk in a future blog posting.

Thanks for your time,

-Klaus