A few months ago I blogged about the challenges of Simple Parameterization in SQL Server. Today I want to continue this discussion by talking about Forced Parameterization in SQL Server.

Forced Parameterization

Simple Parameterization has a number of limitations in SQL Server. It 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

If you still want to have Auto Parameterization performed by SQL Server you can enable Forced Parameterization at the database level:

-- Let's now activate Forced Parameterization on the AdventureWorks2012 database
ALTER DATABASE AdventureWorks2012 SET PARAMETERIZATION FORCED
GO

In that case, SQL Server will always auto parameterize your SQL statement except in the following cases:

• INSERT … EXECUTE
• Prepared SQL Statements
• RECOMPILE
• COMPUTE

Why Forced Parameterization is not always a good choice

Now let’s play with Forced Parameterization in SQL Server. The last listing has already enabled Forced Parameterization for the AdventureWorks2012 database. In the next step I create a copy of the table Sales.SalesOrderHeader and patch the data so that we have a non-linear data distribution in the column CustomerID. In addition I have also created a Non-Clustered Index on that column.

-- Create a copy from the Sales.SalesOrderHeader table
SELECT * INTO Sales.SalesOrderHeader2 FROM Sales.SalesOrderHeader
GO
 
-- Create a Non-Clustered Index on the CustomerID column
CREATE NONCLUSTERED INDEX idx_CustomerID ON Sales.SalesOrderHeader2(CustomerID)
GO

-- "Patch" the data in some way, so that the content of the column "CustomerID" is not evenly distributed across the whole table
UPDATE Sales.SalesOrderHeader2
SET CustomerID = 29675
WHERE SalesOrderID < 60000
GO

The customer with the ID 29675 has a huge amount of orders, and all the other customers just have placed a few orders as you can see in the following picture.

In the next step I execute a simple query that returns all the records for the customer with the ID 22943 – just 3 records. Because the query is before the Tipping Point, SQL Server has chosen an execution plan with a Bookmark Lookup operator. The query produces just 3 logical reads altogether. And because we have enabled Forced Parameterization for the AdventureWorks2012 database, SQL Server has also auto parameterized this SQL statement, so that the execution plan can be reused by the following queries.

Let’s run now another query, where we return all the records for CustomerID 29675. In this case the query returns 16343 rows. When you look again at the execution plan, you can see that this query uses the same execution plan as the previous query. This makes sense, because the query is auto parameterized and SQL Server will find a plan already cached in the Plan Cache. But reusing this execution plan is not really safe, because now we perform the Bookmark Lookup 16343 times – for every row - over and over again. And the query produces 16415 logical reads altogether! Scanning the complete table with a Table Scan produces just 780 logical reads!

That’s the side effect of Forced Parameterization. SQL Server doesn’t care about whether you have a Plan Stability or not. SQL Server just auto parameterizes your SQL statements, and reuses the cached plan over and over again. It doesn’t matter how terrible the execution plan is. But again: you have *forced* SQL Server to do this! Without enabling Forced Parameterization SQL Server would never ever auto parameterize this SQL statement for you, because it’s not safe.

The root cause of this performance problem is of course not the Forced Parameterization. The root cause is that your execution plan contains a Bookmark Lookup. And because of the Bookmark Lookup you have no Plan Stability. No Plan Stability means that you can get different execution plans based on the provided input parameter values. In this specific case sometimes you get a Bookmark Lookup (with a selective input parameter value, and sometimes a Table Scan (with a non-selective input parameter value).

In this situation if you worked on your indexing strategy and you defined a Covering Non-Clustered Index for this query, then the performance problem would also go away. And there would be also no need to enable Forced Parameterization, because with Plan Stability SQL Server would already automatically Auto Parameterize your SQL statement!

Summary

Enabling Forced Parameterization at the database level can be a very dangerous thing. Regardless of whether you have a Plan-Stability or not, SQL Server will always auto parameterize your SQL Statements, and reuse the cached execution plan over and over again. Therefore you have to know in exact detail the shape of your execution plans, and whether they can lead to some performance related problems.

Thanks for your time,

-Klaus