December 26, 2017 at 4:34 pm
If I have a data-driven subscription that will produce 12 reports, will SSRS try to run those 12 reports at the same time or will it run one report at a time until it's produced all 12.
I'm asking this because I want to use this for a case where we currently have 12 subscriptions. The subscriptions are currently spread out so that the report doesn't drag everything else down. This particular report is resource-heavy, so I don't want to end up having it drag down the system by running multiple copies at the same time.
While it's running those reports (whether parallel or sequentially), will it block other subscriptions from being processed (other than any normal blocking that would come about from trying to access the same data)?
I've been trying to find this on-line, but I'm mostly finding articles on how to create a data-driven subscription. If there is something out there, I'm probably using the wrong search terms.
I'm mostly trying to make sure that in trying to switch to what seems like a really good idea, I don't end up causing unexpected problems.
December 27, 2017 at 1:23 pm
My impression is that all 12 will run concurrently. AFAIK this won't cause any extra subscription blockage, just the usual "multiple processes accessing the same data" blockage. These are just my vague understandings, though; hopefully someone else here has a more definitive answer. I also spread out my subscriptions for the reasons you elucidate.
December 27, 2017 at 3:12 pm
Marcia J - Tuesday, December 26, 2017 4:34 PMIf I have a data-driven subscription that will produce 12 reports, will SSRS try to run those 12 reports at the same time or will it run one report at a time until it's produced all 12.I'm asking this because I want to use this for a case where we currently have 12 subscriptions. The subscriptions are currently spread out so that the report doesn't drag everything else down. This particular report is resource-heavy, so I don't want to end up having it drag down the system by running multiple copies at the same time.
While it's running those reports (whether parallel or sequentially), will it block other subscriptions from being processed (other than any normal blocking that would come about from trying to access the same data)?
I've been trying to find this on-line, but I'm mostly finding articles on how to create a data-driven subscription. If there is something out there, I'm probably using the wrong search terms.
I'm mostly trying to make sure that in trying to switch to what seems like a really good idea, I don't end up causing unexpected problems.
Not real sure how you were going to have one data driven subscription for 12 different reports but the processing is done per subscription.
When the subscription job fires, it puts a row in the event table. By default, Reporting service has two threads per CPU for processing subscriptions and these will poll the event table. When an event is found, a row is inserted into the notifications table and the processing starts. The subscriptions in the notification table are processed in a FIFO order.
Sue
December 27, 2017 at 4:09 pm
Sue_H - Wednesday, December 27, 2017 3:12 PMMarcia J - Tuesday, December 26, 2017 4:34 PMIf I have a data-driven subscription that will produce 12 reports, will SSRS try to run those 12 reports at the same time or will it run one report at a time until it's produced all 12.I'm asking this because I want to use this for a case where we currently have 12 subscriptions. The subscriptions are currently spread out so that the report doesn't drag everything else down. This particular report is resource-heavy, so I don't want to end up having it drag down the system by running multiple copies at the same time.
While it's running those reports (whether parallel or sequentially), will it block other subscriptions from being processed (other than any normal blocking that would come about from trying to access the same data)?
I've been trying to find this on-line, but I'm mostly finding articles on how to create a data-driven subscription. If there is something out there, I'm probably using the wrong search terms.
I'm mostly trying to make sure that in trying to switch to what seems like a really good idea, I don't end up causing unexpected problems.
Not real sure how you were going to have one data driven subscription for 12 different reports but the processing is done per subscription.
When the subscription job fires, it puts a row in the event table. By default, Reporting service has two threads per CPU for processing subscriptions and these will poll the event table. When an event is found, a row is inserted into the notifications table and the processing starts. The subscriptions in the notification table are processed in a FIFO order.Sue
I probably did not phrase that well. The initial query for the data-driven subscription will return 12 different rows which will cause the same report to be run 12 times with different sets of parameters.
December 27, 2017 at 4:15 pm
Using different search terms, I did manage to find some more hits that were more pertinent to what I was trying to find out. I found a few that seemed to indicate that the twelve versions of the report would run sequentially. For this particular case, I'm hoping that's true.
I'm going to run some tests and see what actually seems to happen.
December 27, 2017 at 5:07 pm
Got it...thanks. In that case, each row of the query will have a row in the notification table - 12 rows added to the notification table. So from there it works as I said, it just runs through the table in FIFO order using however many threads based on your number of CPUs. How many of those reports would be processing together depends on the order of the rows in the notification table - such as were there other rows inserted before those subscriptions. If these are long running, the reports that are fired off after these 12 would likely be waiting. That's why you can often see the bottlenecks in the notification table - there are only so many threads to process the reports so those would wait until the threads are available to get picked up for processing.
Not sure if this link helps in understanding it better but take a look at this blog post on subscription processing:
SSRS Subscriptions (What goes on "under the hood")
Sue
December 27, 2017 at 7:40 pm
Thanks. That article is very helpful to know what's happening behind the scenes. That's the type of information I was looking for, but I hadn't succeeded in finding it.
December 29, 2017 at 6:58 pm
And it did run the reports in parallel.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply