Introduction to Parameterization in SQL Server

By Bill Graziano on 7 August 2007 | Tags: Performance Tuning , Query Tuning

In a previous article I showed how to look at what query plans are in SQL Server's memory.  In this article I'll look at how they got there and how and when they get reused.  This is commonly called compiling a query plan.  More specifically I'll look at how and when SQL Server can parameterize dynamic SQL to reuse the query plan.

Before executing a batch of SQL statements, SQL Server creates an execution plan (or query plan) for those statements.  This is also referred to as compiling the SQL statements.  One of the benefits of stored procedures is that SQL Serve will reuse compiled query plans.  In certain cases SQL Server will also reuse query plans for raw SQL statements.

My examples all use the AdventureWorks sample database from Microsoft.  The first example shows a very simple query and the text of the plan.  Don't run these samples on a production database server!

DBCC FREEPROCCACHE
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

The DBCC statement clears out all entries from the procedure cache.  That's why you don't want to run this on a production database.  The next statement is statement we're running.  The third SELECT lists everything in the plan cache.  It returns the number of times this plan has been executed, how big the plan is in bytes and the text of the plan.  Also notice that the plan for a fairly simple SQL statement checks in at roughly 57KB.  These things aren't small.

Notice that the integer 56000 has been replaced by a variable in the text of the query plan.  Also notice that the list of parameters (only one parameter in this case) has been inserted at the beginning of the query plan.  This is called "Simple Parameterization" (It was called "Auto Parameterization" in SQL Server 2000).  If we took the middle SQL statement and ran it twice we'd see the execution count increase to two but there would still only be a single plan.

SQL Server can also parameterize queries if they aren't identical.

DBCC FREEPROCCACHE
GO

SELECT	*
FROM	Sales.SalesOrderHeader
WHERE	SalesOrderID = 56000
GO

SELECT	* FROM	Sales.SalesOrderHeader WHERE	SalesOrderID = 56001
GO

select	*
from	Sales.SalesOrderHeader
where	SalesOrderID = 56002
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
---------- ----- ------------------------------------------------------------------------
         3 57344 (@1 int)SELECT * FROM [Sales].[SalesOrderHeader] WHERE [SalesOrderID]=@1

Even though the text of the queries isn't identical and different values are used in the WHERE clause SQL Server still figured out they were the same query.  Now let's look at some examples where it didn't.

DBCC FREEPROCCACHE
GO

SELECT	*
FROM	Sales.SalesOrderHeader
WHERE	SalesOrderID = 56000
GO

SELECT	* FROM	AdventureWorks.Sales.SalesOrderHeader WHERE	SalesOrderID = 56001
GO

declare @i int
set @i = 56004
SELECT *
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = @i
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 65536 declare @i int
                 set @i = 56004
                 SELECT *
                 FROM Sales.SalesOrderHeader
                 WHERE SalesOrderID = @i

         1 57344 (@1 int)SELECT * FROM [AdventureWorks].[Sales].[SalesOrderHeader] WHERE [SalesOrderID]=@1

         1 57344 (@1 int)SELECT * FROM [Sales].[SalesOrderHeader] WHERE [SalesOrderID]=@1

(3 row(s) affected)

Even slight changes in the text of the plan prevent SQL Server from reusing the query plans.  SQL Server places the following restrictions on what types of queries can be parameterized using Simple Parameterization:

If you want SQL Server to parameterize your SQL statements you have three options: stored procedures, sp_executesql or Forced Parameterization.  Stored procedures always have a query plan created and reused.  (Technically they don't always have one but it's close enough for this article.  And they don't always reuse it.  See Query Recompilation in SQL Server 2000 for more details.)  Forced Parameterization will be covered in a future article.  That leaves us with sp_executesql.

Let's start with some statements that don't get parameterized.

DBCC FREEPROCCACHE
GO

SELECT  SUM(LineTotal) AS LineTotal
FROM	Sales.SalesOrderHeader H
JOIN	Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE	H.SalesOrderID = 56000
GO

SELECT  SUM(LineTotal) AS LineTotal
FROM	Sales.SalesOrderHeader H
JOIN	Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE	H.SalesOrderID = 56001
GO

select  stats.execution_count AS exec_count, 
	p.size_in_bytes as [size], 
	LEFT([sql].[text], 80) 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 plan_text
---------- -------------------------------------------------------------------------------
         1 SELECT  SUM(LineTotal) AS LineTotal FROM Sales.SalesOrderHeader H JOIN Sales...
         1 SELECT  SUM(LineTotal) AS LineTotal FROM Sales.SalesOrderHeader H JOIN Sales...

(2 row(s) affected)

Even though you can't see the entire query plan it did generate two different plans for queries that are identical except for the constant in the WHERE.  You can tell SQL Server to parameterize this statement by using the sp_executesql statement.  This explicitly parameterizes the SQL statements.

DBCC FREEPROCCACHE
GO

EXEC sp_executesql N'SELECT  SUM(LineTotal) AS LineTotal
FROM	Sales.SalesOrderHeader H
JOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE	H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', 56000
GO

EXEC sp_executesql N'SELECT  SUM(LineTotal) AS LineTotal
FROM	Sales.SalesOrderHeader H
JOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE	H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', 56005
GO

select  stats.execution_count AS exec_count, 
	LEFT([sql].[text], 80) 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 plan_text
---------- ---------------------------------------------------------------------------------
         2 (@SalesOrderID INT)SELECT SUM(LineTotal) AS LineTotal FROM Sales.SalesOrderHea...

In this article I discussed how SQL Server can parameterize simple queries.  In future articles on this topic I'll discuss Forced Parameterization and preventing problems with parameterization.