All access to tables wit columnstore wait for COLUMNSTORE_LOAD_METADATA

Yuri Gorelik 1

Hello

We have an issue with one of ours SQL servers

Each access to table with columnstore wait for COLUMNSTORE_LOAD_METADATA (about 2-3 sec)

This server is 1 of very similar 15 servers with same configuration and same load

I cannot find any explanation for the wait (LATCH) type in the network

SQL server 2017 CU31 standard edition

Can someone explain or share the same issue?

Thank you
Yuri

Erland Sommarskog 116.2K Reputation points MVP

2023-06-03T21:37:17.42+00:00

You say that this is of 15 servers with the same configuration and load. But they don't all have the same databases with the same schema, do they? Do you get this latch wait with all databases on this instance? (If there is more than one, that is.)

If you think it is an issue with the database itself, you could restore the database to another server, and if the issue appears there as well.
Yuri Gorelik 1 Reputation point

2023-06-04T10:55:00.67+00:00

@Erland Sommarskog
These servers are part of "application horizontal partitioning by customers"
All servers have exact the same schema and almost same load (load balancing by spreading customers on servers according to DB size)
All customer data placed in single DB on server (+ some very small DBA tools DBs)
Erland Sommarskog 116.2K Reputation points MVP

2023-06-04T13:28:56.18+00:00

So there are many databases on the server where you see this latch wait. Have you verified that the wait occurs with many/all databases on the instance? Or is it only one?

I should say that I don't know much about this wait, but I could ask some people that might know. But before I do that I like to have accurate information.

Had you been on a supported version, I would probably have recommended you to open a support case.

I take it that you real issue is not the latch waits as such, but you are seeing a real performance issue?
Yuri Gorelik 1 Reputation point

2023-06-13T08:17:11.13+00:00

@Erland Sommarskog We have single DB on instances
The SQL server version is 2017 CU31

Yes this is a real performance issue all read and write locked on this latch
Yuri Gorelik 1 Reputation point

2023-06-13T08:43:53.3066667+00:00

what is more interesting that the server restart solve the issue for couple of week
If the real issue is large amount of column store metadata the restart should not affect it
Erland Sommarskog 116.2K Reputation points MVP

2023-06-13T21:20:40.09+00:00

Since you are a version out of normal support, that server restart may be your best bet. But I've asked some people I know, to see if they have information on this wait type.
PercyTang-MSFT 12,511 Reputation points Microsoft Vendor

2023-06-16T08:33:03.9866667+00:00

Hi @Yuri Gorelik

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Yuri Gorelik 1 Reputation point

2023-06-18T08:54:35.2966667+00:00

@Erland Sommarskog What do you mean by "out of normal support"
Last version that go to end of support is 2012
Erland Sommarskog 116.2K Reputation points MVP

2023-06-18T10:17:18.48+00:00

SQL 2017 is in extended support. This means that Microsoft only issue security fixes. There will be no further cumulative updates of SQL 2017, and this also means that even if the issue you are seeing is due to a bug, Microsoft will not produce a fix for SQL 2017.

2 answers

José Antonio Campero Morales 105 Reputation points

2023-06-13T22:11:56.77+00:00

The COLUMNSTORE_LOAD_METADATA wait type in SQL Server indicates that there is a delay in loading or refreshing the metadata associated with the columnstore index. This wait type can occur when there are changes to the underlying data in the columnstore index, and the metadata needs to be updated to reflect those changes.

In your case, if every access to a table with a columnstore index is experiencing a 2-3 second wait for COLUMNSTORE_LOAD_METADATA, it suggests that there may be an issue with the metadata loading process on that particular server.

Here are a few things you can consider to troubleshoot and resolve the issue:

Check the server configuration: Ensure that the server has sufficient resources, including CPU, memory, and disk I/O, to handle the columnstore metadata operations efficiently.

Update statistics: Make sure that the statistics for the columnstore index are up to date. Outdated statistics can impact the performance of metadata loading. You can use the UPDATE STATISTICS statement to refresh the statistics for the columnstore index.

Monitor disk performance: Slow disk I/O can contribute to delays in metadata loading. Monitor the disk performance metrics, such as average disk queue length and disk response time, to identify any potential issues.

Check for storage-related problems: If the columnstore index resides on a storage system, ensure that there are no underlying issues with the storage, such as high latency or contention.

Review maintenance activities: If there are any maintenance tasks, such as index rebuilds or statistics updates, running concurrently with user queries, it could impact the metadata loading process. Check for any conflicting maintenance activities and schedule them accordingly.

Consider applying the latest cumulative updates: Although you mentioned that you are already on SQL Server 2017 CU31, it's worth checking if there are any further updates available. Microsoft often releases updates that address performance issues and bug fixes.

If the issue persists after trying these steps, it may be helpful to involve the Microsoft support team or consult with a database administrator who has expertise in troubleshooting SQL Server performance issues. They can analyze the specific configuration and workload of your server to provide further guidance and assistance.
Please sign in to rate this answer.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Erland Sommarskog 116.2K Reputation points MVP

2023-06-14T21:01:52.95+00:00

I got this answer from a senior developer at Microsoft:

This is typically due to poor row group quality (usually when the server is under memory pressure): we have to load metadata for each rowgroup/column during query execution, and if there are a lot of them, it takes time; if there is memory pressure, we can see cache thrashing for the in-memory cache that holds on to these entries. Best to look at memory usage, then CCI index quality.
Please sign in to rate this answer.
Yuri Gorelik 1 Reputation point

2023-06-18T09:12:24.97+00:00

@Erland Sommarskog
This is very interesting point about quality of row groups
Since the "load metadata" sound -> "I have a lot of metadata -> the load take more time -> this is creating blocking chain"

I'd checked the count/avg_size/etc.. for this server and the numbers of row_group count is little higher than on other servers (about 10% more)
So I had tried to fix the issue by reducing the row_group count by reorg
(back ground low priority task running on partitions where average row_group.row_count are low)
This succeed reduce the count of row_groups but do NOT solve the issue.

Maybe I missing some other metrics?
Can you please explain the "low quality"

Erland Sommarskog 116.2K Reputation points MVP

2023-06-18T12:55:31.3233333+00:00

Well, my own experience of columnstore is limited so I can't explain it. But I've reached out to the source from where I got this information, so maybe we can get a clarifiation.

Yuri Gorelik 1 Reputation point

2023-06-22T10:48:36.8533333+00:00

Thank you
This kind of information will be very valuable (I think for whole sql server community)
Very strange that I cannot find any mention about row group quality

Erland Sommarskog 116.2K Reputation points MVP

2023-06-22T20:48:31.8033333+00:00

Unfortunately, I have not heard back, so we may not get further clarity about quality.

Yuri Gorelik 1 Reputation point

2023-08-02T12:44:25.8333333+00:00

@Erland Sommarskog We have opened case to MS support
Getting the same sentence:

This is typically due to poor row group quality (usually when the server is under memory pressure) we have to load metadata for each rowgroup/column during query execution, and if there are a lot of them, it takes time; if there is memory pressure, we can see cache thrashing for the in-memory cache that holds on to these entries. Best to look at memory usage, then CCI index quality.

I will update when we will get more explanation (hope so:)

Erland Sommarskog 116.2K Reputation points MVP

2023-08-02T21:14:33.7433333+00:00

Thanks for the update! Seems like they were in contact with the same developer I was in touch with. :-)

Idan Miller 0 Reputation points

2025-02-04T10:06:03.7366667+00:00

@Erland Sommarskog thank you on your reply, we checked it and we dont see any significant memory pressure.

If you have specific queries for investigate memory pressure ill be glad
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

All access to tables wit columnstore wait for COLUMNSTORE_LOAD_METADATA

2 answers

Your answer