Slow response from database properties caused by system table sys.dm_db_xtp_table_memory_stats

Question

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)

Answer 1

Hi @Jakub Floch ,

SLEEP_TASK is a nonspecific wait type in SQL Server which oocurs when a task sleeps while waiting for a generic event to occur. The SLEEP_TASK wait means that a thread is waiting on a resource or waiting for some events to occur, and could indicate backgroud task scheduling, a query plan exchange operator that isn't tracked by CXPACKET, or it could be a hashing operation that spills to tempdb.

HASH SPILLS mean there is not enough memory to execute the query, and data has to be spilled into temped in order to execute. This usually results in slower performance and space consumption in tempdb. You should determine if hash spills are occuring first. You can run SQL Profiler and enable Hash Warning Events.
For more information: https://learn.microsoft.com/en-us/sql/relational-databases/event-classes/hash-warning-event-class?view=sql-server-ver15

Answer 2

Given the ramifications on SSMS, I would open a support case. If nothing else, it would be better if SSMS gave up on the query, and possible called it demand if you actually wanted to see the value.

The SLEEP_TASK does not look good. But it could be that you are starved for memory and you are waiting for memory to be available, despite the wait type. (Presumably the sleep occurs inside the internal function that implements the DMV.)