processing dimensions in SSAS 2019 takes ten times longer than in SSAS 2017

Hi @Holger Kühn ,

Welcome to Microsoft Q&A!

I suggest that you could use some tools to monitor SSAS. I am used to analysis by SQL Server Profiler, but please noted that this feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

During SSAS multi-dimensional MOLAP processing, SSAS will opens concurrent connections to each data source to execute SELECT queries to get data from the relational database, then create the indexes files in SSAS data folder. We need to look at the Profiler trace to see how much time is spent on reading relational database data, and how much time is creating aggregations, indexes, etc. At the end of processing is the commit. Knowing how much time the processing command spends at each stage can help us isolate the cause of the problem. The best way to get familiar with Profiler trace on processing is to run a simple processing command, then look at the events in Profiler.

Best regards,
Carrin

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hi Carrin,

Thanks for your tips, but as I wrote there is only the processing from one dimension on a freshly depoyed cube, so not aggregations and such. The SELECT query is at 543 ms, so this is not explaininng the 30sec processing.

As I can not attatch xel-files directly you can find them here. The xlsx is showing the SELECT - Query in Profiler:

https://kumo.dachs.blog/processing-dimensions-in-ssas-2019-takes-ten-times.zip

The processing was started with the following XMLA:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
      <Object>
        <DatabaseID>SSAS_Test</DatabaseID>
        <DimensionID>Abrechnungsaktion</DimensionID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

Holger

Hi @Holger Kühn , thanks for your reply. Processing Analysis Services dimensions and partitions is a resource-intensive operation. It should use all of the resources—CPU, memory, and disk I/O—available to Analysis Services. There are a few settings that can impact process parallelism of processing jobs on SSAS. Too little parallelism can cause underutilization of SSAS resources, and too much parallelism can cause relational database become too busy, or even error out. And also, maybe you could try to update it to latest CU 14. For more information, please refer to SQL Server Analysis Services Processing Order Options and Why an Upgrade can Cause Performance Regressions (Dear SQL DBA Episode 9).

3: https://www.littlekendra.com/2016/07/21/why-can-an-upgrade-cause-performance-regression-dear-sql-dba-episode-9/ 5: /api/attachments/168109-1-2.png?platform=QnA