未加星标

Row Mode Memory Grant Feedback in SQL Server 2019

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

In this article, I’ll be exploring another new feature with SQL Server 2019, row mode memory grant feedback, along with a retrospective on adaptive query processing, examples and more.

With each version of SQL Server, Microsoft is providing enhancements to the query optimizer. In SQL Server 2017, we enjoyed the query processing improvements in terms of Adaptive Query processing. From time to time, we might face issues related to the cardinality estimates in execution plans that result in a Query performance issue. SQL Server 2017 Adaptive Query Processing helps to provide better cardinality estimation with the feedback mechanism. Using this, we can get a better execution plan, memory allotment, join selection etc.

Below are the Adaptive Query Processing modes in SQL Server 2017

Interleaved Executions Batch Mode Memory Grant Feedback Batch Mode Adaptive Joins
Row Mode Memory Grant Feedback in SQL Server 2019

Before we move, we need to understand the issue that exists with the memory allocation. To demonstrate, we will use WideWorldImporters sample database in SQL Server 2019.

Default Compatibility level is 150 in SQL Server 2019. Therefore, first, set the database compatibility level to SQL Server 2017 (level 140) for ‘ WideWorldImporters’ database. USE [master]GO ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 140 GO Run the below query and view the Actual execution plan in SQL Server Management Studio. SELECT OD.CustomerID,OD.CustomerPurchaseOrderNumber, OD.InternalComments,OL.Quantity,OL.UnitPrice FROM [Sales].[Orders] OD INNER JOIN [Sales].[OrderLines] OL ON OD.OrderID = OL.OrderID ORDER BY OD.[Comments]
Row Mode Memory Grant Feedback in SQL Server 2019

We can see a warning icon in the Select operator in the actual execution plan. If we hover the mouse over the operator, we can see the detailed warning message.


Row Mode Memory Grant Feedback in SQL Server 2019

In the properties section, we can see the detailed memory utilization. Notice the difference in the desired memory and the granted memory.


Row Mode Memory Grant Feedback in SQL Server 2019

We can see the warning message ‘ The query memory grant detected “ExcessiveGrant”, which may impact the reliability. Grant size: Initial 208800 KB, Final 208800 KB, Used 5880 KB.’

As per the error message, the query memory granted 203 MB while it used only 5.74 MB memory. We have granted excessive memory that might create performance issues in the highly OLTP system. If the excessive memory grant is appearing too very frequent, it might leave less space for the buffer cache, query plan etc.

In SQL Server 2019, Microsoft is providing a further update to the intelligent query processing for such excessive memory grants. This feature was already available in the Azure SQL database. This feature is called ‘Row Memory Grant Feedback’.

‘Row mode memory grant feedback’ is an extension to the’ batch mode memory grant feedback’ feature in SQL Server 2017. This feature adjusts the memory grant sizes for both batch and row mode operator.

We need to have a database with Compatibility level 150 in order to use this functionality.

USE [master] GO ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 150 GO

Verify the database compatibility level is set to SQL Server 2019 (150)

Select Compatibility_level,namefrom sys.databases where name='WideWorldImporters'
Row Mode Memory Grant Feedback in SQL Server 2019

Run the query that we executed, in the above example, for SQL Server 2017 and view the actual execution plan.

You can notice the warning here “The query memory grant detected “ExcessiveGrant”, which may impact the reliability. Grant size: Initial 212200 KB, Final 212200 KB, Used 5872 KB.”


Row Mode Memory Grant Feedback in SQL Server 2019

We again got the excessive memory grant message in SQL Server 2019. Does it mean that there is no enhancement in SQL Server 2019 as well?

We cannot see any improvement in SQL Server 2019 behavior also until now. Well, run query one more time and observe the behavior.


Row Mode Memory Grant Feedback in SQL Server 2019

There is no warning error message in the actual execution plan, this time, so let us see the detailed property of this select operator in the execution plan,


Row Mode Memory Grant Feedback in SQL Server 2019

We can see here that only 11.87 MB memory allocation as compared to 207 MB memory in the first execution.

Click on the Select operator and view the property. You can notice that the Desired and the Granted memory are same.


Row Mode Memory Grant Feedback in SQL Server 2019

We get the following benefits from this memory grant feedback

If SQL Server identifies that granted memory is more than the used memory, in next run memory grant feedback calculates the memory again, therefore, you can see less memory in further runs There might be scenarios related to the spill disk issue. In that case, also memory grant feedback helps to recalculate the memory and grant appropriate memory in further runs Extended events for Row Mode Memory Grant feedback

We can view the ‘row mode memory grant feedback’ with the query_post_execution_showplan extended event. You can see the description in the bottom description section as

‘Occurs after a SQL statement is executed. This event returns an XML representation of the actual query plan. Using this event can create significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time.’
Row Mode Memory Grant Feedback in SQL Server 2019

You can find it in the execution category as shown here.


Row Mode Memory Grant Feedback in SQL Server 2019
CREATE EVENT SESSION [Monitor_memory_Grant_Feedback] ON SERVER ADD EVENT sqlserver.query_post_execution_showplan ADD TARGET package0.event_file(SET filename=N'Monitor_memory_Grant_Feedback') 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
Row Mode Memory Grant Feedback in SQL Server 2019

In the ‘query_post_execution_showplan’ Xevent, we can find new attributes to show this memory grant feedback.

Attribute

Value

Description

IsMemoryGrantFeedbackAdjusted

Yes or No

Yes: Value It shows that Memory grant feedback is used in the query.

No: value- it shows that no memory grant feedback adjusted.

We can find the value from the below table

LastRequestedMemory

Memory in KB

Memory in KB. If it is first executed, the value is 0

We can see below values in the ‘IsMemoryGrantFeedbackAdjusted’ along with Yes or No.

Value Descripti

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Row Mode Memory Grant Feedback in SQL Server 2019
本站链接:https://www.codesec.net/view/621154.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(150)