SQL SERVER – Extended Event to Capture Memory Grant Feedback

It has been over 3 weeks since SQL Server 2019 launched and after reading my recent blog posts about the Memory Grant Feedback, I was approached by one of the leading banks in Europe to help them build an efficient architecture and performance-optimized system Comprehensive Database Performance Health Check. In this blog post, we will learn about the extended event to capture memory grant feedback. You can read more about Memory Grant Feedback over here, here, here, here, here and here.

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory0-800x237

Brief History

While I worked with the client to get ready for their migration of SQL Server 2019, we realized that there are few queries giving us trouble with regards to performance. As I have done a similar task, just a week before for another client, I exactly knew the problem for my client. I have previously blogged about it why I have disabled the Memory Grant Feedback at the Query Level.

However, as this time we realized that there are many queries which were facing the same issue, we decided to identify those queries with the help of the Extended Event. Once we identified the queries with the extended events, we were able to tune the queries together.

Capture Memory Grant Feedback

Let us see how we can create an extended event to Capture Memory Grant Feedback.

You can create an extended event by running the following script:

CREATE EVENT SESSION [MemoryGrantFeedback] ON SERVER
ADD EVENT sqlserver.memory_grant_feedback_loop_disabled(
ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.memory_grant_updated_by_feedback(
ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30
 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

However, if you want to use the wizard, you can follow the steps displaced here in the images.

Solarwinds

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory1

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory2

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory3

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory4

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory5

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory6

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory7

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory8

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory9

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory10

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory11

Now once the extended event is created and enabled. Right-click on it and select “Watch Live Data“. Initial it will be empty. Now go to the blog post mentioned here and create the stored procedure displayed in the blog post. Next, run the following stored procedure 20 times by executing the following code. As we are running two stored procedure the total execution will be of 40 times.

EXEC GetStockDetails 120
EXEC GetStockDetails 223
GO 20

Continue to watch the live data while the stored procedure is executing and you will see various events being recorded in the live stream. Even though the stored procedure is executed a total of 40 times, it will record lesser events in the extended events as it will stop the feedback loop.

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory12

You will notice that after 32nd execution, the memory grant update by feedback is disabled.

SQL SERVER - Extended Event to Capture Memory Grant Feedback capturememory13

If you look carefully in the details area, you can also find the stored procedure which is creating this behavior.

At my client’s place, we identified all such stored procedures and tuned them one at a time. Currently, their system is running extremely smooth and very efficient.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – MemoryGrantInfo Property Explanation
Next Post
SQL SERVER – Database Scoped Configurations

Related Posts

Leave a Reply

Menu