Database Parameterization
Published Aug 22 2021 12:42 AM 3,404 Views
Microsoft

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 repeated@email.com is repeated in 899766 rows (of 1 million rows table), while the other values are repeated maximum 8 times only:

 

select email , count(*) from infotbl  group by email order by count(*) desc

 

1.JPG

 

First, I created an index on the email column using below statement:

 

create index ix_emailsender  on infotbl (email) 

 

 

Logically, the Query Optimizer will choose index seek for all values except for repeated@email.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:

 

select * from infotbl where email = 'tar0.554553@email.com'

 

2.jpg

 

 

select * from infotbl where email = 'repeated@email.com'

 

3.jpg

 

How to check the density of an index ?

By running show_statistics console command as the following:

 

dbcc show_statistics (infotbl, ix_emailsender)

 

 

4.jpg

 

All density in the result above is 1 / distinct values , it is same as the result of the query: 

 

select 1/convert(decimal(30,20),count(distinct email)) from infotbl;

 

 

 

Parameterization Forced:

If I repeat the same queries but after changing the parameterization to Forced, first by running the Alter database:

 

ALTER DATABASE [mydatabase] SET PARAMETERIZATION FORCED WITH NO_WAIT
--I may need to free the procedure cache by running : 
dbcc freeproccache()

 

 

Now,  I executed the Queries again,  first:

 

select *  from infotbl where email = 'repeated@email.com'

 

5.jpg

 

Then  if I run the second Query, the Query optimizer will use the reserved execution plan “that has been created by Query 1”:

 

select * from infotbl where email = 'tar0.554553@email.com'

 

6.jpg

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:

 

create index IX_createdon on infotbl (createdon) include (email) where createdon  => '1-1-2021'

 

 

With Simple parametrization, all Queries that search for  values in 2021 range,  will use index Seek:

 

select  email from infotbl where createdon  = '2021-06-08 11:00:22.513'

 

7.jpg

 

And others are as below example, will use Clustered index scan instead:

 

select  email from infotbl where createdon  = '2020-06-09 15:02:02.280'

 

8.jpg

 

Now if I set Parametrization Forced again and execute the same two Queries, I will have the same execution plan, as below:

 

select  email from infotbl where createdon  = '2021-06-08 11:00:22.513'

 

10.jpg

 

If you view the Execution Plan’s XML, you will find the Waring UnmatchedIndexes="true" as the following :

 

          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="200">
            <UnmatchedIndexes>
              <Parameterization>
                <Object Database="[tarasheedb]" Schema="[dbo]" Table="[infotbl]" Index="[IX_createdon]" />
              </Parameterization>
            </UnmatchedIndexes>
            <Warnings UnmatchedIndexes="true" />

 

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:

 

SELECT objtype, text   
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_sql_text(plan_handle)   
WHERE   text like '%select *  from infotbl where email%' and not (text like '%SELECT objtype, text %')

 

 

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.

9.jpg

 

 

Co-Authors
Version history
Last update:
‎Aug 22 2021 12:42 AM
Updated by: