SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data-driven subscriptions - sequential or parallel.


Data-driven subscriptions - sequential or parallel.

Author
Message
Marcia J
Marcia J
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4789 Visits: 2204
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.
doug.brown
doug.brown
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4947 Visits: 22588
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.
Sue_H
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72286 Visits: 14916
Marcia J - Tuesday, December 26, 2017 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.

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




Marcia J
Marcia J
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4789 Visits: 2204
Sue_H - Wednesday, December 27, 2017 3:12 PM
Marcia J - Tuesday, December 26, 2017 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.

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.

Marcia J
Marcia J
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4789 Visits: 2204
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.
Sue_H
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72286 Visits: 14916


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



Marcia J
Marcia J
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4789 Visits: 2204
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.
Marcia J
Marcia J
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4789 Visits: 2204
And it did run the reports in parallel.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search