Ladies and gentlemen ...drumroll... SQL Server 2022 is among us! New features and improvements!

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

Now the news is that SQL Server 2022 will bring improvements in this area! 
A new feature called "Parameter Sensitive Plan Optimization" infact will purposely cache multiple "parameter sensitive active query plans" per stored procedure or parameterized query and the best plan will be used! 
Nice Idea!

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.

 

That's all for today mates! 
This was just the welcome post of sqlserverperformace blog to the new version of SQL Server.

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!

Comments

Post a Comment

I Post più popolari

SQL Server, execution plan and the lazy spool (clearly explained)

Image
Hi Guys, Welcome back! In one of the last posts we talked about Table Spool . We had explained what Spool are for by telling the somewhat curious story of the halloween problem . Here we specifically talked about a type of spool called Eager Spool   You can look to the execution plan below where the eager Spool is used to avoid the halloween problem in classic query of the "salary increase".. i suggest Well, if the story of the eager spool intrigued you, today instead we will speak about another type of Spool, the Lazy Spool . The Lazy spool Let's take a small step back. So, what is a Spool operation?  As we have already understood a spool operation is simply a temporary storage of data . Data are read from a source table and stored inside a worktable in the Tempdb database . While an Eager Spool is used to prevent the Halloween problem, the Lazy Spool is instead used to store data that will later be needed again when running a query . So, when a Lazy spool is used?  ...
Read more

SQL Server, datetime vs. datetime2

Image
Hi guys, This time, in this post we will speak about datetime and datetime2 datatypes. We will talk about the pros and cons. We will talk about all the differences between the two types of data. Write me in the comments which type of data do you prefer between the two. Enjoy the reading!     Datetime Vs. Datetime2 First of all, it must be said that the datatype datetime is certainly the best known native format for storing a date in SQL Server. However, Microsoft has also been introducing a new type of data for many years now, also used to manage dates. It is in fact from SQL server 2008 that it is present in datetime2 data type. This "new" type of data was born primarily to have higher accuracy . Let's in fact talk about ... precision ! Datetime Certainly the datetime data type is not very precise and certainly there are many cases in which its precision is simply not sufficient. Let's see it with an example. Open the SQL Management studio (SSMS) and write the fo...
Read more

La clausola NOLOCK. Approfondiamo e facciamo chiarezza!

Image
Ehi ragazzi! La domanda è di quelle importanti! Siete pronti per scoprire tutto ma proprio tutto sulla clausola NOLOCK?   Se la riposta è si, allora seguitemi! Oggi ne spiegheremo il funzionamento in maniera approfondita. Faremo una introduzione al concetto di tipi di LOCK Ed infine vedremo come mai una SELECT ne può bloccare un'altra. Non vi nascondo che il motivo di questo articolo è fare chiarezza. Su questo argomento si sentono spesso affermazioni “curiose” ma tra le tante questa è la prima: “ uso la SELECT con l’opzione WITH(LOCK) così non blocco le altre tabelle ”.   Ma, è proprio vero quanto affermato? Beh oggi facciamo il punto!   Buona lettura!     La clausola NOLOCK Diciamo subito per iniziare che cos’è la NOLOCK. NOLOCK è il nome di una clausola che è possibile specificare all’interno di uno statement di tipo SELECT utilizzando la seguente sintassi: SELECT * FROM TABELLA WITH (NOLOCK) Si noti che non possiamo ...
Read more