SQL Server 2022’s Parameter Sensitive Plan Optimisation feature

By Luke
17th May 2022

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”

 

Customer Table

 

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.

 

Get in touch

<< Back to Articles

Join Our Newsletter

Contact Email  *
First Name 
Last Name 
*Required Fields
I agree to the  Privacy Policy and  Terms of Use 

Here's what other people think

Google Rating
5.0
High standard, professional service. I've worked with Welldata for a number of years and always found their DBA's to be knowledgeable and proactive. Throw any question at them and it will be resolved, it gives real peace of mind knowing you have a partner you can rely on from emergencies to general questions. They also like a challenge and we do throw a few curved balls at them.read more
Paul Miller
Paul Miller
12:34 27 Aug 21
The support I receive from Well Data is extremely responsive. Richard Parsons is my contact and keeps me informed daily on the health of our SQL Databases. Whenever I ask for assistance from Well Data it is quick and professional.read more
Kay Riley
Kay Riley
13:41 19 Aug 21
Really know their stuff and their response times are very good. They provide us with a vital service.
Andy Cole
Andy Cole
13:36 19 Aug 21
The guys at WellData really know their stuff. We wanted a quick route to doing something complex and they sorted it for us very quickly and to a high standard. Very professional, no ego, just excellent and to the point consultancy.read more
Steve Goacher
Steve Goacher
11:31 14 Nov 19
I have worked with the WellData team for many years now and they have always delivered.I would highly recommend them to any business looking for best-value outsourced Expert DBA’s.Being able to offer a rolling contract on the basis that if they don’t deliver you can leave shows their confidence and commitment.Their professionalism and knowledge is second to none.read more
Petro Bartoszyk
Petro Bartoszyk
08:51 18 Oct 19
A very dedicated team of extremely professional and knowledgeable experts
Giulia Iannucci
Giulia Iannucci
14:08 16 Nov 18
We use Welldata for SQL support and are very happy with their service.
David Sadler
David Sadler
09:40 15 Nov 18
Really know their stuff and their response times are very good.
Andy Cole
Andy Cole
11:45 06 Nov 18
Our partnership with WellData has enabled us to resolve critical database problems. Their attitude and service makes them a natural extension to our own technology team, which extends our capability in a critical area of our business.read more
Steve Fenton
Steve Fenton
10:56 05 Nov 18
Having worked with the team at Welldata for approaching 17 years, my company and I have always found them professional, reliable and generally a great firm to work with.read more
John Lynes
John Lynes
14:00 04 Oct 17
WellData provide an excellent level of support and their team are very knowledgeable and always go the extra mile. I would wholeheartedly recommend them.read more
Stefan Parrott
Stefan Parrott
13:12 19 Sep 17