Hello,
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?
December 7, 2022 at 11:34 am
Quick question, do the affected reports' queries have anything particular in common?
😎
So far you have done what I would do in such a situation, next step is to look at similarities in the affected queries.
December 7, 2022 at 7:14 pm
Have you tried running the queries with a hint to force the use of the legacy cardinality estimator?
Also... when you migrated the data to the new server, did indexes get either rebuilt or reorganized in the process?
And, what's the row modification counter say about the statistics involved with the slower queries?
Could be any of the above and could also be none of the above. It's a list of things to check for.
For example, an index rebuild or reorganize can reduce the number of pages for the index. That just might cause the query to go under the cost threshold of parallelism and run slower.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 7, 2022 at 10:23 pm
Can you share the details of the two plans, and the query?
Quick update about this in case someone runs into the same problem and stumbles upon this thread:
We finally managed to figure out what the problem was: after we had upgraded, we had played around a bit with the new “Calculation Groups” feature, but in order to do that, we also had to change the “Discourage Implicit Measures” to “true” on model level.
We had removed the “Calculation Groups” again, but hadn’t changed the “Discourage Implicit Measures” setting back to “false”, which apparently caused the different query plans / slower report performance in Excel.
So now after changing that setting back to “false”, the reports on the upgraded model are using the “old” query plans again, and are also fast.
Needless to say, we wont be using Calculation Groups any time soon!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply