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.

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.

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.

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

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)