The pain of Forced Parameterization in SQL Server

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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:

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.

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.

An uneven data distribution - quite common...

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.

The SQL statement was auto parameterized

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!

The auto parameterized execution plan was reused...

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.

Like or share it to get the source code.

Thanks for your time,

-Klaus

2 Comments

  • Maneesh

    Gr8 information and Great mind.

    Here Select statement should already have been auto parameterized as it doesn’t contain any Join, union etc. but as per my understanding from article because of data skewness , it couldn’t. means plan instability.

    Please also share information of dmvs like sys.dm_os_waiting_tasks and sys.dm_os_memory_objects dmvs as there is very less information available over net.

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top