This article is contributed. See the original author and article here.
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
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'
select * from infotbl where email = 'repeated@email.com'
How to check the density of an index ?
By running show_statistics console command as the following:
dbcc show_statistics (infotbl, ix_emailsender)
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'
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'
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'
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'
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'
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.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments