question

JakubFloch-9352 avatar image
0 Votes"
JakubFloch-9352 asked JakubFloch-4346 commented

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

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:

138069-image.png


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)


sql-server-general
image.png (49.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered JakubFloch-4346 commented

Hi @JakubFloch-9352,

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://docs.microsoft.com/en-us/sql/relational-databases/event-classes/hash-warning-event-class?view=sql-server-ver15

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi JakubFloch-9352,


If the reply above was helpful please mark as accepted answer so it can be helpful for other community members with same questions.

0 Votes 0 ·

Hi, memory should not be the issue but we will check events you mentioned. Interesting thing is that after instance reboot, everything was fine for one week approximately. Now the issue is same.

0 Votes 0 ·

thank you

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered JakubFloch-4346 commented

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.)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, we will try to open support case. Thank you.

0 Votes 0 ·