1

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?

msta42a
  • 3,601
  • 1
  • 4
  • 14

1 Answers1

0

If you are reporting for the previous week could you automate ssrs to output the reports on Sunday night?

MrHappyHead
  • 442
  • 3
  • 8
  • This is a good tip. In fact, in most cases, stores generate a report for the past week. Unfortunately, I cannot use it in my case. The stores also operate on Sunday (which must be included in the report), on this day we run the FULL (TABULAR) process and at night our daily ETL process is launched, topping up data from the current day. It starts a few minutes after midnight and ends +/- 6:00 - 7:00. On Monday morning I don't have enough time for automatic generation and dispatch. – msta42a Oct 07 '20 at 10:37