Scaling up on a virtual machine

Question

Scaling up on a virtual machine

agerard 65681

Ten Centuries

Points: 1024
More actions
September 1, 2017 at 9:45 am

#328234

Hi folks,
We have an SSRS 2016 server processing quite a lot of subscriptions. It's a VM with 4vCPUs. I've been querying the ExecutionLog and have found that we are running at a very low level of parallelism. The MaxQueueThreads setting doesn't seem to have an effect. Adding more vCPUs doesn't seem to have an effect. Can't get more than 4 subs to run at a time... With 4 vCPU cores I would have thought it would hit at least 8 simultaneous, which is 2 per core--the supposed default. It's maxxing out at about 35% CPU utilization. Something other than available threads and CPU resources seems to be capping the max degree of parallelism.
As we use data-driven subscriptions (SQL Enterprise only), scaling out would be hugely expensive. And scaling up doesn't seem to be possible... I know vCPUs are not the same as real CPUs... Do we need to go to a physical machine to get more subscriptions to process simultaneously?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32069 More actions · Answer 1

Hello,
If you can't optimize the reports Troubleshooting Reports: Report Performance
you might want to check SSRS subscriptions under the hood
It mentions MaxQueueThreads in the reporting services configuration file

agerard 65681 Ten Centuries Points: 1024 More actions · Answer 2

Thanks Jo

We have gone past the usefulness of the MaxQueueThreads setting. It's currently set higher than the default, but we're not reaching anywhere near the number of threads it's set to. I mentioned there are 4 vCPUs... I had up-sized it from 2 to 4 vCPU cores and this change had no effect on the max number of threads that it is able to use / how much time all of our daily subscriptions take. With 4 cores, the max cpu utlization peaked at about 40%, while with 2 cores, it gets up to about 70 or 80%. So I'm thinking is SSRS just not well suited to a VM? vCPUs are not like "real" cpus. Does anyone have experience with high-volume SSRS applications on a VM?

As far as optimizing the reports themselves--that is of course the best suggestion, but no further gains will be gotten there. They all have to export to Excel and that in and of itself is taking up about 90% of the time each execution of the subscription takes. And from what Microsoft says--there's no way to speed that process up.

--Andy

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

agerard 65681 - Tuesday, September 5, 2017 1:59 PM
Thanks Jo
We have gone past the usefulness of the MaxQueueThreads setting. It's currently set higher than the default, but we're not reaching anywhere near the number of threads it's set to. I mentioned there are 4 vCPUs... I had up-sized it from 2 to 4 vCPU cores and this change had no effect on the max number of threads that it is able to use / how much time all of our daily subscriptions take. With 4 cores, the max cpu utlization peaked at about 40%, while with 2 cores, it gets up to about 70 or 80%. So I'm thinking is SSRS just not well suited to a VM? vCPUs are not like "real" cpus. Does anyone have experience with high-volume SSRS applications on a VM?
As far as optimizing the reports themselves--that is of course the best suggestion, but no further gains will be gotten there. They all have to export to Excel and that in and of itself is taking up about 90% of the time each execution of the subscription takes. And from what Microsoft says--there's no way to speed that process up.
--Andy

What are you using to accumulate the data for each report? Stored Procedures"? Matrix? Scripts? SSRS object code? Or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jo Pattyn SSC-Dedicated Points: 32069 More actions · Answer 4

Can't really recommend further steps, as you checked a lot.
Anything odd on the VM(host) side? Like not running in high-performance mode, overcommitment, old drivers ...
Maybe ssrs is not cpu-limited but memory limited?

agerard 65681 Ten Centuries Points: 1024 More actions · Answer 5

We have 16GB of RAM on the machine and there doesn't appear to be memory pressure. I'm not sure about "high-performance mode", I don't know that that's a setting on our hypervisor. I'll have to look into that.

Jeff, the data is accumulated and served to the report using stored procs. The RDL does fairly extensive grouping on its own. I've looked through the execution log and found that data retrieval and report processing take a small fraction of the amount of time involved, and rendering is the lion's share. We have to render the reports to Excel, and according to microsoft this process is largely not optimizable. The reports we are creating contain a large amount of data. Creating Excel files takes a while... No way around that.

The issue that I am trying to resolve is that the report server will not process more than 4 or 5 subscriptions simultaneously. Adding more vCPUs did not allow for more simultaneous / parallel subscription processing and did not speed up the overall time to process the subscriptions. I am looking for insights into why this is the case. Does SSRS just not scale up well on a VM? Would a physical machine help? Does SSRS just not scale up well at all? Do we need to scale out? I'm trying to avoid scaling out since the expense of another server with SQL Enterprise would be huge.