At Ignite 2021, Microsoft announced the release of SQL Server 2022, and I want to touch on one of the features I’m excited about in this release.
Parameter Sensitive Plan Optimisation – with “SQL Server 2022’s Parameter Sensitive Plan Optimisation” feature, SQL Server automatically enables the generation of multiple active cached plans for a single parameterised statement. These cached execution plans will accommodate different data sizes based on the provided runtime parameter values.”
This feature could be the answer to something that has plagued a lot of SQL servers throughout the years, Parameter Sniffing.
So what’s Parameter Sniffing?
Parameter sniffing is a term used by DBAs for SQL Server using the same Query Plan for wildly different parameterised statements. Here’s an example.
We have a table called “Customer”
And we have a parameterised query;
Select FirstName from Customer where VIP=@Answer
Let’s say someone is looking for all VIP Customers, the query is executed with the @Answer parameter being “Yes” SQL Server Query Optimiser states, there are only 2 VIPs meeting that result, so let’s do an index seek. This is great, SQL Server finds my rows very quickly, and everyone is happy.
Now the statement gets executed again, where the @Answer parameter is “No” SQL Server already has this Query in its Plan Cache, and states “I know what to do with this; let’s do an index seek”. This isn’t great; it reads the index then the pointer to the row to the data; for each entry with “No” that’s 12 reads. Doing a table scan in this instance would have only been 8 reads, which is more optimal.
The Performance Impact
Now obviously, this is a very crude example, but as you can imagine, with tables having thousands/millions of rows, we want to be scanning & seeking in the right places. And having SQL Server behave as we intended can save a lot of time and resources. Historically, the main ways of resolving parameter sniffing issues were by indexing or adding the OPTION(RECOMPILE) to the SQL Query/Stored Procedure. These were often not ideal resolutions; as you can imagine, having to recompile your query for every execution will have its own performance overhead.
Watch this Space
I’m really hoping Microsoft gets this right; it will be interesting to see this in action when 2022 is available for testing. However, I suspect that with SQL Server caching multiple plans for the same query, we might start seeing plan cache bloat. For instance, if SQL Server is caching a new plan for each individual parameter, depending on how many variations of parameters there are, you could end up with 100’s of plans for a single parameterised query, which is going to be using up lots of memory. Hopefully, this won’t be the case, and there will be some communication with the cardinality estimator to store the parameters in ranges or groups (or something).
Worried about your SQL Server Performance? Think you’re a victim of parameter sniffing? Give us a call or get in touch by clicking the button below for SQL Server DBA Support.
<< Back to Articles
