SQL Server Simple and Forced Parametrization

Oguzhan Yigit
Published by Oguzhan
Category : Azure
11/11/2018

With this post, I just wanted to share something I have stumbled onto when using Azure Advisor service and related to the optimization of a cloud based SQL Server database. However, it applies to any version of Microsoft SQL Server, either cloud or on-premise.
It all started when I randomly browsed the Azure portal and checked the Azure Advisor service : there I saw a recommendation marked as having a high impact. My first reaction was “wow, how can I have missed that ?!”.

azure performance recommendation

The performance recommendations detail screen showed it was about non-parameterized queries.

peformance recommendation detail

So the recommendation is quite self-explanatory and is to use forced parameterization on the database, but let’s refresh our memories about what we are talking about here.

 

Parameterized queries and SQL Server Execution Plan reuse

Each time the database receives a new T-SQL query, SQL Server engine works with various processes to send back the query result. Let’s focus on the main 2 steps which are “Relational engine processes” and “Storage engine processes”.
Relational engine processes are in charge of:

  • First, parsing the query: in this step, the engine parses the query and resolve the object names such as tables, columns, aliases… Once parsed, the query is compiled.
  • Then, finding the “best” estimated execution plan (thanks to the Query Optimizer) : basically, the fastest the better. The Query Optimizer evaluates many parameters like CPU, I/O costs but it also refers to statistics stored in SQL Server. It might also consider that it will take too much time to calculate the optimal execution plan and decide to reply with a plan which is trivial. To make it simple, keep in mind that the query optimizer tries to get the best execution plan in the lowest timeframe. In any case, calculating the query execution plan has a high cost, so SQL Server stores the plans to reuse them.

Using parameters in T-SQL queries can increase the reusability of the calculated execution plans, thus reducing the induced cost of calculation.

 

SQL Server parameterized query configuration

A SQL server database can be configured with “Simple parameterization” or “Forced parameterization”. Depending on this configuration, SQL handles incoming queries in a different way. The default setting is Simple parameterization.

 

Simple parameterization

If you try to execute a statement without parameters on your database, the relational engine will try to parameterize the query: it means that SQL Server will substitute any literal value inside the query with parameters. For instance, when sent to the database engine, the following query is parameterized as follows :

parameterization by sql server

The intended goal is to maximize the chance of the resulting query to match one of the existing execution plans. However, the relational engine may have difficulty determining which expressions can be parameterized and therefore only a subset of queries can be parameterized with Simple Parameterization. You can’t choose which query will be and which won’t be. So, if you have complex queries, you should parameterize them before executing them against the database engine.
Refer to this link for further inputs : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186219%28v%3dsql.105%29

 

Forced parameterization

By specifying forced parameterization, and under certain limitations, the engine will parameterize all the literals within the SELECT, INSERT, UPDATE and DELETE statements. The engine will have a higher chance of reusing execution plans under the forced parameterization context.
To get the limitations and more details, check this post about forced parameterization
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175037(v=sql.105)

 

Please note that depending on your data and/or your queries, it might not be beneficial to setup Forced parameterization. If you profile all your queries, you may notice performance degradations due to forced parameterization on some queries. In this case, you can still define exceptions by using specific plan guides. Read more about plan guides with the following post : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191275%28v%3dsql.105%29