This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
On SQL Server, there is a database setting called parameterization.
parameterization has two values, Simple (the default) or Forced.
I will share here some details and examples to simplify the concept of parameterization, and try to describe how it impacts Database performance:
In my scenario, the distribution of the data is not even on column email of my example table infotbl, when I execute the Query below it shows that the value firstname.lastname@example.org is repeated in 899766 rows (of 1 million rows table), while the other values are repeated maximum 8 times only:
First, I created an index on the email column using below statement:
Logically, the Query Optimizer will choose index seek for all values except for email@example.com, it will choose a Clustered Index scan instead.
For example, if I execute the following two queries, both will have a different execution plan:
How to check the density of an index ?
By running show_statistics console command as the following:
All density in the result above is 1 / distinct values , it is same as the result of the query:
If I repeat the same queries but after changing the parameterization to Forced, first by running the Alter database:
Now, I executed the Queries again, first:
Then if I run the second Query, the Query optimizer will use the reserved execution plan “that has been created by Query 1”:
Then all other executions will be slower than expected, because there will be always an Index Scan every time whatever the size of result is and the selectivity of the search value.
Other Disadvantage of Forced parameterization, the Filtered Index:
If I create a filtered index on the ModifiedOn column, as an example when all Application’s important queries and Reports are accessing only data of 2021.
Filtered Index script:
With Simple parametrization, all Queries that search for values in 2021 range, will use index Seek:
And others are as below example, will use Clustered index scan instead:
Now if I set Parametrization Forced again and execute the same two Queries, I will have the same execution plan, as below:
If you view the Execution Plan’s XML, you will find the Waring UnmatchedIndexes="true" as the following :
This is because the Query Optimizer cannot use the Filtered index when the parametrization is Forced.
What is the good thing in Parameterization forced option?
The following script will show the execution plans of my Select Queries:
With parameterization Forced, Only one Adhoc Execution plan exists in the plan cache, and the prepared execution plan that will be reused every time the query executed “again”, this will save the time of recompiling overhead every time, and decreases the size of the Procedure cache.