We recently upgraded our SSAS Tabular model from compatibility level SQL Server 2014 (1103) to compatibility level SQL Server 2019 (1500), and while we are noticing big performance improvements in processing times, we are also noticing some performance decreases in certain reports, where actions that ran almost instantly in the old version, are now taking minutes to complete, or even time out.
At the moment we have a SQL 2019 SSAS test instance, where we have both an 1103 version and a 1500 version of the model deployed, both linked to the same source data, so both the server and the data are the same, only the model version is different so we can test as accurately as possible.
During the testing we noticed certain Excel reports (our end users are mainly using Excel on the SSAS tabular model as reporting tool) took a lot longer on the upgraded version, so we turned on a SQL profiler, and captured an MDX query that was slower on the upgraded version (note: running the report on both versions gave the same MDX-query, so the query itself didn't change with the upgraded version).
Once we had the MDX-query, we used “DAX Studio” to run it on both version while turning on the “Query Plan” and “Server Timings” options, and noticed the MDX query used 2 very different Query Plans / server Timings outputs if we compared it on the old and upgraded version of the model.
We tried clearing the cache of the upgraded model in the hope it would generate new/better query plans on the new version of the model, like it did on the old version, but we had no luck with that.
We also upgrade the SQL 2019 server to the latest Cumulative Update (CU18), which also didn't help.
So here is our question: does anyone have any idea, why the same MDX query, on the same dataset, but on different SSAS tabular versions (1103 vs 1500) would generate (worse) query plans on the newer version, and how to optimize/fix this?