Hi Guys,
Breaking news!
We are in the midst of the top Microsoft event called Microsoft Ignite and many of us were waiting for some data on the successor of SQL Server 2019.
Since yesterday the new version of SQL Server has a name: SQL Server 2022
yes, the new SQL Server 2022 was been unveiled yesterday morning during Executive Vice President Scott Guthrie's keynote.
The product is only in private preview and so few details are available.
As our blog talks about performance what we're interested in are improvements to the key performance features of the database engine.
We will try to understand from a performance point of view what new features await us.
We are waiting to get our hands on the first available version (and this should happen soon)
We can't wait to start testing .. so follow me!
New features and improvements in SQL Server 2022
For some years now, Microsoft has directed the development of the optimizer in the direction of increasing its ability to modify the execution plan of a query after each of its execution.
In fact, we have already noted that the ability of the same execution plan to have changes leads to many advantages.
One example is the memory grant feedback feature that was added in SQL Server 2017 and completed in SQL Server 2019 (for the row mode, we blogged here): If a given query does not have enough memory or requires too much memory for sort and join operations, the memory granted is changed in subsequent executions.
Following the same philosophy SQL server 2022 will propose a remedy (hopefully effective) to the problem caused by parameter sniffing.
This could therefore be one of the improvements that would lead to a significant increase in performance.
Just two words to remind you what it is!
Do you know when you have a query that normally takes a few seconds but instead takes minutes to complete from time to time?
The most common cause of this is due to the phenomenon called "parameter sniffing"
What happens behind the scenes?
It happens that:
The first time a query is run SQL Server generates an execution plan.
This execution plan is based on the parameter values passed in that initial execution.
This execution plan is stored in the plan cache.
Generating an execution plan is an expensive operation that requires resources and time and therefore Microsoft have chosen to recycle it where possible.
When the same query is run again (i.e. with the same hash value) we can take advantage of having an execution plan ready.
However, it can also happen that if very different parameters are passed, the used plan is not optimal. Microsoft call these plans "Parameter sensitive plans".
Parameter Sensitive Plan Optimization
News on Intelligent Query Processing (IQP)
Other announced additional IQP enhancement are related to maximum degree of parallelism (MaxDOP) and cardinality estimates (CE) (*). On both of the feature, Query stored will be used to create a feedback mechanism to adapt the execution plan of a query after each of its execution.
(*)We blogged about cardinality estimates many times for example: here, here and here
Finally, we have also enhancements to memory grant feedback. Persistence and new algorithms will be added to smoothly handle memory grant fluctuations.
Look forward to the next post where we will talk about theory: we will see what the grouped aggregate pushdown is and we will do our usual tests!
Luca Biondi @ SQLServerPerformance blog 2019-2021 all rights reserved!
Help me to share knowledge on my blog 
Next post:
Previous post: SQL Server, the "Distinct clause" competition. Oops i did it again! ...from 24 seconds to 6 milliseconds!