• Madeira Team

Parameterization Part 6: Simple vs. Forced Parameterization

This is the sixth post in the “Parameterization” series. In the previous posts I explained what parameterization is, how plan caching works in SQL Server, what parameter sniffing is, when it’s good and when it’s bad, and also some common mistakes related to parameterization and how to avoid them.

In this post I would like to cover an important feature related to parameterization, but yet rarely known and understood. The feature is called parameterization. Great name, isn’t it? I’m not talking here about the concept of parameterization, which is the subject of the whole series. I’m talking about a database property called “Parameterization”, which can have two values: “Simple” and “Forced”.

Parameterization-Part-6-Picture-1

The default value is “Simple”, and it is rarely changed to “Forced”. Most DBAs aren’t even aware of the property, which means they don’t understand what SQL Server does in the “Simple” mode, which means they might run into problematic scenarios they can’t explain. So let’s explain this property…

The “Parameterization” property controls the way SQL Server handles literals in query predicates. In some cases, SQL Server might decide to replace a literal value with a parameter during query optimization. For example, in the following query:

Transact-SQL

SELECT Id , Name , Country , LastPurchaseDate FROM Marketing.Customers WHERE Country = N'IL';

1

2

3

4

5

6

7

8

9

SELECT

Id ,

Name ,

Country ,

LastPurchaseDate

FROM

Marketing.Customers

WHERE

Country = N'IL';

SQL Server might decide to replace N’IL’ with a parameter, so the query would actually look like that:

Transact-SQL

SELECT Id , Name , Country , LastPurchaseDate FROM Marketing.Customers WHERE Country = @0;

1

2

3

4

5

6

7

8

9

SELECT

Id ,

Name ,

Country ,

LastPurchaseDate

FROM

Marketing.Customers

WHERE

Country = @0;

Now, why would SQL Server want to do something like that? In order to improve plan reuse and reduce the number of compilations. If your application sends ad-hoc queries to SQL Server, where the same query is executed many times with different literal values, then replacing the literal value with a parameter can significantly reduce the number of compilations in the system and the overhead associated with them.

The next time we execute the query above with a different literal, like this:

Transact-SQL

SELECT Id , Name , Country , LastPurchaseDate FROM Marketing.Customers WHERE Country = N'FR';

1

2

3

4

5

6

7

8