All access to tables wit columnstore wait for COLUMNSTORE_LOAD_METADATA

Yuri Gorelik 1 Reputation point
2023-06-03T18:05:39.55+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,324 questions
{count} votes

2 answers

Sort by: Most helpful
  1. 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.


  2. Erland Sommarskog 106.5K Reputation points
    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.