This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi all,
we've build a medium sized tabular modell with ~10 GB of data. This runs on SSAS 2022 Enterprise Edition, the VM running the Instance has 16 vCores and 512 GB of memory (the host has got 64 pCores and 2048 GB of memory). We see great performance as long only a few users are accessing the machine with instant response <1s, but a massive performance drop on a realistic group of users accessing the model (up to several minutes response time) and the CPU is going up to 100% for several minutes. The memory consumption is stable and is not anywhere near the available memory.
Any idea where to start looking for the bottleneck?
Many thanks!
Stefan
Hi @Birkenheuer, Stefan,
Thank you for the information and based on information you provided, it appears to be a typical case of CPU saturation due to concurrency in SSAS Tabular, even when memory usage is not an issue. In Tabular, performance can be strong for a single user, but multiple users often cause the VertiPaq engine to experience SE callbacks and threadpool contention.
To address this, it’s best to capture a workload trace (using Flight Recorder or Query Log) to identify which DAX queries have the highest storage engine CPU usage. Then, review those queries for iterative expressions like SUMX, FILTER, or ADDCOLUMNS, and try to use aggregated patterns instead where possible.
Additionally, check the engine configuration adjusting MaxParallelism and ThreadPool settings can help with throughput when many users are active, and make sure AutoDetectRelationshipsDuringQuery is turned off. These steps are key to improving concurrency scaling.
Hope this helps. Please reach out for further assistance.
Thank you.
Hi @Birkenheuer, Stefan,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
Hi @Birkenheuer, Stefan,
We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved? If you still require support, please let us know, we are happy to assist you.
Thank you.
Hi, no the problem is not resolved. A ticket has been raised with MS Support and their conclusion is that we are hitting natural limits of SSAS tabular and we should revert back all tweaked settings (MaxParallelism and ThreadPool) to default, although changing has not made any differences. So currently it looks like that a relatively small amount of data is driving SSAS tabular to it's limits very quickly on random queries.