We have successfully upgraded our server version to SQL Server 2019.
One of the expected features that we dreamed about was memory-optimized TempDB metadata and we turned it on, first we decided to check how it would behave on our test environment and what was our surprise when, after a couple of days, our test server stopped and in the log we found the following messages
Disallowing page allocations for database 'tempdb' due to insufficient memory in the resource pool 'default'.
XTP failed page allocation due to memory pressure: FAIL_PAGE_ALLOCATION 8
Error: 701, Severity: 17, State: 153.
We read the recommendations on enabling Resource Governor and periodically running the sys.sp_xtp_force_gc service procedure, but I want to understand who was the reason for the high memory consumption when using tempdb.
Perhaps someone has analyzed this problem and worked out solutions to find the code that leads to such an action, or long-term transactions that do not make it possible to free memory, please share scripts or best practices to identify problem areas. Perhaps there are some settings that few people know about or came up with experimentally. Thanks to everyone who will respond.
We tried to turn on Resource Governor and periodically, when utilizing memory, run sys.sp_xtp_force_gc, it all really works, but I want to understand the causes of the problems and minimize them by changing the code or additional server settings.
