SQL Server 2019, Row Mode Memory Grant Feedback

Hy Guys,


Are you ready for another post? Today we will discuss about another new feature introduced by the last SQL Server 2019 that is called Memory Grant FeedBack for Row Mode.

Enjoy the reading mate!

 

 

A recap!

Just to recap what we are talking about in these last posts and where we have arrived.

In the past posts we have seen what are the new features introduced by SQL 2019. We have addressed all but one.

SQL 2019 Intelligent Query Processing

We have talked about:

So, what subject do we still have to talk about? well..


Memory Grant FeedBack for Row Mode

Many times we have focused on performance. We want to get a Query that is quick to execute and that reads as few pages of memory as possible.

This certainly remains true!

However, there is another aspect to focus on: the use of memory.

Obviously memory is a limited resource and the more the queries require memory the less we will be able to execute at the same time.

We can see how much memory our Query is using simply from the execution plan looking to the Memory Grant row:


The SQL Server optimizer also warns when the estimated memory for execution turns out to be greater than the actual one: we always check if a yellow triangle appears in the execution plan.

The question is. How can we solve problems of this type?

SQL Server 2017 has introduced the memory grant feedback feature to avoid this problem but it only worked only in batch mode.
Today SQL Server 2019 also introduces it for the more common Row mode!

This time I will explain the details of how it works during the test! 


The Test

As usually we will do some tests to verify all this theory and also to go into more detail, so follow me mate!

As usual we will use our test tables that have already been used many times.

We want to test both SQL Server 2017 and SQL Server 2019. Since we already have SQL 2019 installed, we will set a compatibility level of 140 to replicate the behavior of SQL Server 2017. 

 

TEST ON SQL SERVER 2017

Before each test we also empty the cache. 


ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE memorygrant SET COMPATIBILITY_LEVEL = 140

Then type this following very simple Query:


SELECT * FROM ORDRIG C ORDER BY C.IDCUSTOMER

Running the query we obtain this execution plan:

You can notice that a yellow triangles appear.
These warnings should always be read!
 
But what happened?
 
SQL server initially estimated an use of 6656 KB of memory while at the end of the execution it realize that actually used only 144 KB.
This is a big waste of memory which could briefly lead to its exhaustion.
 
 
Attention as you have noticed, already with simple queries, it is very easy to run into problems of excessive use of memory. SQL Server optimizer infact has some problem in memory estimation.


TEST ON SQL SERVER 2019

Now let's move to SQL server 2019 to see what happens!

We run the commands below to clear the cache and to set the native compatibility level:


ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE memorygrant SET COMPATIBILITY_LEVEL = 150

Then run the original T-SQL "simple" command:


SELECT * FROM ORDRIG C ORDER BY C.IDCUSTOMER

Running this command we have this execution plan:

 

The warning sign is still here and memory used is yet 6656KB!

Oh, what's up? what can be missing?

We forgot to set the "magic" option:


ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON

Then run again the original Query again for the second time 


SELECT * FROM ORDRIG C ORDER BY C.IDCUSTOMER

Urrà! 

This time in the execution plan we no longer have the warning sign!

 


...and used memory drop to 1536 KB..

This option works, we used less memory!

But how does it works in detail? a.k.a. "...some more theory"

Using the option ROW_MODE_MEMORY_GRANT_FEEDBACK we have activated a  feedback mechanism: After each run used memory is compared to the Granted memory and then the Granted memory is adjusted accordingly. This is the trick!

More in detail we can see that in the MemoryGrantInfo section a new property called IsMemoryGrantFeedBackAdjusted is added.

This property can have 5 different values: NoFirstExecution, NoAccurate Grant, NoFeedback disabled, YesAdjusting and YesStable.

Looking to the photo above, When we ran the query for the second time, its value changed from NoFirstExection to YesAdjusting!

If now we execute our Query again (so for the third time) we can see that we reach a stable state: In this case the value of the IsMemoryGrantFeedBackAdjusted is YesStable.


Tipically the IsMemoryGrantFeedBackAdjusted states used are these:

SQL Server IsMemoryGrantFeedBackAdjusted

But it can happen that SQL Server never fails to reach a stable state. For example because the memory used keeps growing or decreasing continuously.

In this case SQL Server can disables memory grant feedback. IsMemoryGrantFeedBackAdjusted property will be set to NoFeedbackdisabled.

Finally the last value that the property IsMemoryGrantFeedBackAdjusted cound have is NoAccurateGrant. Well simply feedback is not activated because SQL Server nailed the correct memory! (used memory is at least 50% of the granted memory and there is not spill to disk)


P.S. Could this be another reason to switch to SQL 2019?


That's all for today mate,
I hope you enjoyed the post, if so stay tuned and follow my blog and on linkedin!
Luca

Luca Biondi @ SQLServerPerformance blog 2021!







Next post:

Previous post: SQL Server, better performance with the NOCOUNT option

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