We encountered issue with accessing this system table sys.dm_db_xtp_table_memory_stats, which is part of dialog window database properties in SSMS. When we click on properties of random database, we have to wait 5 - 10 minutes for dialog window to show up. Properties are unusable this way.
Any ideas to solve this problem?
We found that this query is running during that wait time. And the actual wait was sleep_task:
SELECT
isnull((select convert(decimal(18,2),(sum(tms.memory_allocated_for_table_kb) + sum(tms.memory_allocated_for_indexes_kb)))
from [sys].[dm_db_xtp_table_memory_stats] tms), 0.00) AS [MemoryAllocatedToMemoryOptimizedObjectsInKB]
It does not matter what we select from the table, it is slow even with:
select * from [sys].[dm_db_xtp_table_memory_stats]
Here is screen shot of sesssion waits:
MSSQL version:
SQL SERVER version:
Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64)
May 27 2021 17:34:14
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )
(The same issue was on our 2017 edition)