Forced Parameterization

In the previous post I investigated what was going on when SQL Server decides to auto-parameterize your query. What I didn’t mention is that this is also called simple parameterization. There is another category of parameterization called “Forced”.

In forced parameterization the rules are a bit different. In the previous post I mentioned the two conceptual stages of auto-parameterization. In the first stage the query is auto-parameterized by the LPE. In the second stage the query optimizer looks to see if the choice of the literal value could result in different plans being chosen. If not, the query is deemed “safe” and when run the parameterized query will be used and cached and a shell query plan will be created for the text literal query.

Forced parameterization rules allow for the parameterization of a larger range of queries than simple-parameterization. Some queries that would not be considered for parameterization in the first stage can be considered for forced parameterization. Also, in the second stage, potential for different plans will not result in the query optimizer deeming the plan unsafe. Conceptually, no longer should one think of parameterization occurring in two stages. The query is either parameterized or it is not. What happens inside the storage engine is a mystery to me but perfmon gives us a clue. In the SQL Server:SQL Server Statistics object there are counters to track parameterization information. Auto-Param Attemps/sec, Failed Auto-Params/sec, Forced Parameterizations/sec, Safe Auto-Params/sec and Unsafe Auto-Params/sec are the counters of interest to us.

When operating under simple-parameterization Auto-Param counters can keep track of the attempts to auto-parameterize, failures to auto-parameterized and if those plans are deemed safe or not, revealing the two stage concept I talked about in my last post. However when using forced Parameterization the Auto-Param counters are quiet.

To investigate forced parameterization I’ve created a schema with three tables, two normal tables and one intermediate table. In these tables I’ve inserted data that is skewed so that different plans will be produced with a query that differs only by the literal constant. It can be reproduced by using this:
USE AutoParam
GO
–DROP TABLE Parent
CREATE TABLE Parent
(ID int IDENTITY (1,1), Col1 int, Col2 char(2))
GO
–DROP TABLE ParentChild
CREATE TABLE ParentChild
(ParentID int, ChildID int)
GO
–DROP TABLE Child
CREATE TABLE Child
(ID int IDENTITY (1,1), Col1 int, Col2 char(2))
GO
–insert data into Parent
DECLARE @c int
SET @c = 0
WHILE @c < 1000
BEGIN
INSERT INTO Parent (Col1,Col2) VALUES (@c,’aa’)
SET @c = @c + 1
END
GO
INSERT INTO Parent (Col1,Col2) VALUES (1000,’aa’)
GO 1000
INSERT INTO Parent (Col1,Col2) VALUES (2000,’aa’)
GO 1000
INSERT INTO Parent (Col1,Col2) VALUES (3000,’aa’)
GO 1000
INSERT INTO Parent (Col1,Col2) VALUES (4000,’aa’)
GO 1000
INSERT INTO Parent (Col1,Col2) VALUES (5000,’aa’)
GO 1000
INSERT INTO Parent (Col1,Col2) VALUES (6000,’aa’)
GO 1000
INSERT INTO Parent (Col1,Col2) VALUES (7000,’aa’)
GO 1000
INSERT INTO Parent (Col1,Col2) VALUES (8000,’aa’)
GO 1000
INSERT INTO Parent (Col1,Col2) VALUES (9000,’aa’)
GO 1000
CREATE INDEX ParentID ON Parent(ID)
CREATE INDEX ParentChildParentID ON ParentChild(ParentID)
CREATE INDEX ParentChildChildID ON ParentChild(ChildID)
CREATE INDEX ChildID ON Child(ID)
CREATE INDEX ParentCol1 ON Parent(Col1)
CREATE INDEX ChildCol1 ON Child(Col1)
GO
–insert data into child
DECLARE @c int
SET @c = 0
WHILE @c < 10000
BEGIN
INSERT INTO Child (Col1,Col2) VALUES (@c,’cc’)
SET @c = @c + 1
END
GO
–insert data into the intermediate table
INSERT INTO ParentChild (ParentID,ChildID)
SELECT P.ID,C.ID FROM Parent P
INNER JOIN Child C
ON C.Col1 = P.Col1

With forced parameterization turned off (simple is on) I want to see if the queries have been auto-parameterized by the LPE. Recall that in the auto-parameterized plan the StmtSimple element has an attribute that looks something like this:
ParameterizedText=”(@1 tinyint)SELECT [Col1] FROM [Test] WHERE [Col1]=@1″

To see the estimated execution plans turn on SHOWPLAN_XML and run Query A and Query B:
SET SHOWPLAN_XML ON
GO

Query A:
SELECT P.Col2 FROM Child C
LEFT JOIN ParentChild PC
ON C.ID = PC.ChildID
LEFT JOIN Parent P
ON P.ID = PC.ParentID
WHERE P.Col1 = 1

Query B
SELECT P.Col2 FROM Child C
LEFT JOIN ParentChild PC
ON C.ID = PC.ChildID
LEFT JOIN Parent P
ON P.ID = PC.ParentID
WHERE P.Col1 = 1000

The ParameterizedText attribute does not show up in either estimated execution plan, so these queries have not been auto-parameterized by the LPE. Monitoring perfmon counters for this query show that there was an auto-parameterization attempt but that the attempt failed. The safe and unsafe counters didn’t register.

In another query window turn on forced parameterization (I like to use a separate window so that I don’t have to keep setting SHOWPLAN_XML on and off.)
ALTER DATABASE AutoParam SET PARAMETERIZATION FORCED

With forced parameterization on (and SHOWPLAN_XML still on) run Query A and Query B as before and look at the estimated execution plans. There is a difference in the process here though. After running each select statement clear the procedure cache again. This is important because under forced parameterization the second estimated plan will be the same as the first, even though under simple parameterization the statements result in two different plans. Forced parameterization is already taking effect when you are looking at the estimated plans because the second one you look at will have used the plan of the first. Freeing the procedure cache allows the second one to generate a plan without being influenced by the first.

Query A
Query B

When you look at each of these plans the ParameterizedText attribute should exist in the StmtSimple element. Recall that under simple-parameterization these plans didn’t have a ParemeterizedText attribute and the perfmon counters showed a failed attempt at auto-parameterization. Now the auto-param perfmon counters are quiet and the forced parameterization counter registers a hit.

The final step is to turn off SHOWPLAN_XML, clear the procedure cache and run the two queries again to see if they have been deemed safe. This time don’t free the procedure cache between running the queries.

SET SHOWPLAN_XML OFF
GO
DBCC FREEPROCCACHE
GO

Run each of the queries above independently so they are separate batches. Once that is done look at the plan cache. If you are running this on SQL Server 2005 see the previous post on how to fix this query to work on that version.

SELECT t.text,p.objtype,p.cacheobjtype,p.plan_handle,qp.query_plan,s.query_hash,s.query_plan_hash
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
LEFT JOIN sys.dm_exec_query_stats s
ON s.plan_handle = p.plan_handle
ORDER BY s.query_plan_hash

This shows that both queries have resulted in shell queries (these are the queries on lines 2 and 3. Looking at the execution plans of these shell queries each has a pointer to the real execution plan:

Query A:
ParameterizedPlanHandle=”0x06000A00FF175F3140217686010000000000000000000000″
Query B: ParameterizedPlanHandle=”0x06000A00FF175F3140217686010000000000000000000000″

For all you know I simply pasted the same ParameterizedPlanHandle twice :), but I didn’t. I copied these directly from the XML of the query plans. These plan handles match the plan_handle of the 4th query in the list. This is the parameterized query.

This is an example of what forced parameterization can do. It can take a query that fails auto-parameterization in simple mode and parameterize it. Again, what happens in the guts of SQL Server is hard to know but I think this shows pretty well that the two stage concept of auto-parameterization does not work for forced parameterization.

Forced parameterization has some definite pitfalls to be aware of. In the above select statements, it is easy to see how a query that would perform better under one execution plan could easily get saddled by one that is already in cache. The first query uses nested loops and bookmark lookups. It also only returned one record. If the second query (which returns 1000 records) gets stuck using the plan from the first query the nested loops will have more records to process and the bookmark lookups will have be exercised for all 1000 records. However, the plan for the second query uses table and index scans which is appropriate when the number of records being returned justify table scans. If the first query has to use the second plan then a number of table scans will have to be performed to find just one record. Not a wise use of resources.

Next blog entry will look at an optimization feature that also helps keep the plan cache from becoming bloated: Optimize for Adhoc Workloads.

One thought on “Forced Parameterization

Comments are closed.