In our company we have "office Monday", that means every office/shop/department (circa 2000+ distinct user) should generate their reports, especially shops (SSRS with connection to tabular 1500 compatibility level). We are facing very high resource usage in 3+ hours range (CPU 100% - multiple cores) and the session queue is growing up and never flush. A report that takes 2 minutes off-peak can take more than an hour due to overload. We have on-premise machine. For the rest of the week problem, didn't occured (workload is 10 time lower, usage of CPU in peak is less than 30%).
Unfortunately, from a business point of view, we cannot spread the load over the remaining days of the week. We also have no influence on how many users will run the reports at a given time (load distribution throughout the day).
What we have tried already:
- rewrite queries in reports from old MDX to Dax (always checking the performance of single query with Serving Timing in Dax Studio)
- rewrite measures to less expenssive
- Tuning our model (for example. change to the less consuming datatype, removing unused columns)
- We can't migrate this model to Azure.
- We can't make any hardware changes on this machine.
Maybe we can change some server properties? Model properties? Connections properties? Can we manipulate for which reports / queries Tabular should keep the cache if out of resources? For example, for a group of store reports which we know will generate many similar inquiries (e.g. only the store number will change) Any advices?
If you are reporting for the previous week could you automate ssrs to output the reports on Sunday night?