Today is May the fourth and I’ll start by saying: Happy Star Wars day to all. Since it is May the fourth I figured a Star Wars themed post would be nice.
So today I’m posting about “the Force” as in Forced Parameterization. If you’ve ever queried using the same query but different parameters, you may have noticed that there are multiple plans in cache by default. As an example if you ran:
SELECT ProductID, Name
FROM Production.Product
WHERE isnull(Weight, 5) between 1 and 20
and then ran:
SELECT ProductID, Name
FROM Production.Product
WHERE isnull(Weight, 5) between 20 and 50
This could store two plans because the isnull function doesn’t allow “Simple parameterization” to occur. With that said, You may think it’d be much, MUCH better to write this as “WHERE Weight between 1 and 20 or Weight is null” but this also negates Simple parameterization.
Let’s take a look at the query plans. You can see the query text for each. Note the parameters.
Because the values for query 1 and query 2 are not parameterized, any time the values change a new plan will be cached which consumes more memory in the instance.
How can we fix this? Alter the database setting “PARAMETERIZATION” to Forced.
Now that we’ve changed from Simple to Forced we should check the query plan again.
Woohoo! All the values have been parameterized. All good right? Not so fast. Everything and I mean EVERYTHING in the database world is “It depends”.
We’ve condensed the query plans to save memory. All should be good but think about queries that could have greatly different result returned. If query 3 returned 2% of the table for one set of values and 80% of the table for another set of values this could be very bad because you’d essentially have a parameter sniffing problem. Be sure to test before blindly changing any settings and May the Fourth Forced be with you.
#SQLServer #AzureSQLDatabase