Interesting problem came up today on #sqlhelp. A trivial plan results in multiple prepared Execution plans.
To begin with let’s understand parameterization quickly. It is a mechanism that the optimizer uses to create an execution plan for Ad hoc queries (not store procedures) that enables plan reuse. In other words when a query is changed by only the literal (value) being passed in the where clause the body of the TSQL doesn’t change and therefore the step involved in executing the code doesn’t change either. In this case the optimizer decides to reuse a previously generated (and available in plan cache memory) execution plan instead of creating a new plan just because the value has changed. This is a good thing except when we encounter parameter sniffing.
By default the database has Simple parameterization enabled. Which means that for basic queries that do not include things like joins, sub queries , group by etc. the optimizer tries to create and reuse the plan where possible.
Take a look at the below query
First we clear the procedure cache and confirm we don’t have any plans lying around.
Next we execute a very simple query as shown below
Next we increase the number in the where clause by 1 and execute the query again.
Notice how we have two different rows (stubs for each query) in the results but the plan cache shows the same single execution plan as before
This is what the stub looks like
This is what the execution plan looks like
See how the where clause has been parametrized by variable @1 int. Pay close attention to the datatype here it is INT. Because INT is the smallest datatype that case hold the value 244550 from the initial
Now what happens if the first query had a value of say 2. We see two stubs but we also see two execution plans
Why? If we look at the execution plan we can see that the datatype for the variables for each prepared query is different and therefore we end up with two different plans.
So if we have to use Simple parameterization start with the bigger value first.
References
https://www.sqlpassion.at/archive/2015/04/27/the-pain-of-simple-parameterization-in-sql-server/
Please Consider Subscribing
