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”.
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
9
SELECT
Id ,
Name ,
Country ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = N'FR';
SQL Server will recognize that it has the same template as the previous query and reuse the parameterized plan already in cache.
But we already know that this is not necessarily a good thing. If the same query requires different plans for different parameter values, then replacing its literals with parameters might actually degrade performance. It might be dangerous!
This is exactly why the “Parameterization” property exists. Under the “Simple” mode, SQL Server will only parameterize queries when it is certain that there is only one plan for the query regardless of the value of the literal being parameterized. In other words, it will parameterize only queries, which have a trivial plan.
If you don’t know what a trivial plan is, then let me explain this concept as well. The query optimizer recognizes a class of very simple queries, for which only a single plan exists, and for which there is no reason to start the full optimization process. An example is a query on a single table with a single predicate on the clustered primary key…
Transact-SQL
SELECT Id , Name , Country , LastPurchaseDate FROM Marketing.Customers WHERE Id = 1234;
1
2
3
4
5
6
7
8
9
SELECT
Id ,
Name ,
Country ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Id = 1234;
In this case, the plan involves a single clustered index seek, and it is obvious that the same plan will be used for any literal value. You can recognize a trivial plan by examining the “Optimization Level” property of the “SELECT” operator in the graphical execution plan.
Optimization level can be either “Trivial” or “Full”.
So under simple parameterization mode, SQL Server will only parameterize queries with a trivial plan. This is good, because there is no risk of hurting performance as a result of reusing the wrong plan. If the same plan is good for all literal values, then it makes no sense to compile the query again and again for each value. This is what SQL Server does by default.
So for the first query with the predicate on the “Country” column, SQL Server will not parameterize it (because its optimization level is full and not trivial). But for the second query with the predicate on the “Id” column (the primary key), SQL Server will parameterize it and reuse the plan for any literal value.
What happens if we change the database property to “Forced”?
Transact-SQL
ALTER DATABASE MyDatabase SET PARAMETERIZATION FORCED;
1
2
3
4
ALTER DATABASE
MyDatabase
SET
PARAMETERIZATION FORCED;
First of all, changing the parameterization property for a database removes all the current plans associated with the database from the plan cache. Under the forced parameterization mode, SQL Server will parameterize every query regardless of its plan being trivial or full. There are some limitations to this, which you can read about here, but generally speaking, SQL Server will attempt to parameterize all queries. Again, this can be dangerous, and this is why you should be very careful when changing this property and test it thoroughly before you do it in production.
But in some cases, forced parameterization can significantly improve the overall performance of the database. If your application uses a lot of ad hoc queries, which are compiled many times, and in most cases the same plan is generated again and again, then changing to forced parameterization can substantially reduce the resource consumption associated with excessive compilations and improve performance.
The problem with this database property is that it affects the whole database. First, as I wrote before, changing the property removes all plans associated with the database from cache. This is a bad thing, because there are probably many great plans in cache, and it would be a waste to throw them away just to create them again later. But the main problem is that forced parameterization might be a great solution for some queries, but in most cases this is not the best solution for all queries in the database. We need to be able to apply forced parameterization on specific queries…
Fortunately, we can. In order to achieve that we are going to use two system stored procedures. The first is sys.sp_get_query_template. This stored procedure requires a query text as input, and it returns two output parameters – the query template text and the definition of the parameters (names and data types) used in the template. It converts the query into a template in exactly the same way as it would be converted under forced parameterization.
Transact-SQL
DECLARE @Statement AS NVARCHAR(MAX) , @Params AS NVARCHAR(MAX); EXECUTE sys.sp_get_query_template @querytext = N' SELECT Id , Name , Country , LastPurchaseDate FROM Marketing.Customers WHERE Country = N''IL''; ' , @templatetext = @Statement OUTPUT , @parameters = @Params OUTPUT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE
@Statement AS NVARCHAR(MAX) ,
@Params AS NVARCHAR(MAX);
EXECUTE sys.sp_get_query_template
@querytext =
N'
SELECT
Id ,
Name ,
Country ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = N''IL'';
' ,
@templatetext = @Statement OUTPUT ,
@parameters = @Params OUTPUT;
After you run the above code, the @Statement variable will contain the following query template:
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;
And the @Params variable will contain the definition of the @0 parameter, which is “@0 NVARCHAR(4000)”.
Once we have the query template and the parameters definition, we can apply forced parameterization to the query template by using a plan guide. Plan guides are objects in the database that let you optimize specific queries by applying query hints at runtime. Instead of changing the code in the application and apply query hints in the code, you can create a plan guide in the server, and whenever the relevant query comes along, the query hint will be added just before it is executed. One of the types of plan guides is a template plan guide, which applies parameterization (either simple or forced) to all the queries with the same query template.
So in our example we are going to create a template plan guide for the template that we received in the previous step from sys.sp_get_query_template, which will apply forced parameterization to all the queries with the same template.
Transact-SQL
EXECUTE sys.sp_create_plan_guide @name = N'CustomersByCountryTemplate' , @stmt = @Statement , @type = N'TEMPLATE' , @module_or_batch = NULL , @params = @Params , @hints = N'OPTION (PARAMETERIZATION FORCED)';
1
2
3
4
5
6
7
EXECUTE sys.sp_create_plan_guide
@name = N'CustomersByCountryTemplate' ,
@stmt = @Statement ,
@type = N'TEMPLATE' ,
@module_or_batch = NULL ,
@params = @Params ,
@hints = N'OPTION (PARAMETERIZATION FORCED)';
From now on, even when the database is in the Simple parameterization mode, each time we execute the same query with different countries, the “OPTION (PARAMETERIZATION FORCED)” query hint will be added to the query at runtime, the query will be parameterized, and the same execution plan will be reused.
This way we can control parameterization at a more granular level and optimize the database much better than by just applying one of the parameterization modes at the database level.
But you have to keep in mind that plan guides are dangerous. Not just template plan guides are dangerous. All plan guides are. They are dangerous for the same reason that triggers are dangerous. They are hidden. They do what they do behind the scenes, and it’s not obvious to track them and to even be aware that they exist. A plan guide that you create today might actually do the work and improve performance for specific query. But one year from now, it might do the opposite and prevent the optimizer from coming up with a better plan. There is a good chance that the plan guide will still be there one year from now, because no one will remember that it’s there. And when someone will try to troubleshoot the performance of the query, they will have a hard time figuring out that there is a plan guide behind the scenes that is responsible for that.
Having said that, plan guides are a great feature when used properly and responsibly. If your application sends ad-hoc queries and suffers from excessive compilations of specific queries, and you can’t modify the code, then template plan guides are probably the way to go. But test it carefully, document it well, and reevaluate them once in a while.