October 20, 2017 at 9:05 am
Hi,
I have about 5,000 reports that get sent out each morning. Using data-driven subscriptions, we send a custom tailored copy of each report to each client. The issue is the order in which data becomes ready and subscriptions are processed and put into the FIFO queue more or less means we have no ability to spread the subscription processing resources around evenly to all customers / all different types of reports.
is there some reasonably safe way to manipulate the Notifications or Events tables (or the procs that actually pick the work) to prioritize them in some way other than straight FIFO?
Example:
Reports 1, 2 and 3 go to 1000 people each and are put in the queue first.
Reports 4, 5, 6, 7, 8, and 9 go to 100 people each and are put in the queue next.
No one gets reports 4 through 9 until 1, 2, and 3 are done.
We'd like to process subscriptions based on the percentage of total for each different report that's in the queue. So if reports 1, 2, and 3 contain 75% of the total in the queue, they would get 75% of the threads, not 100%.
Am I looking for open-source answers on a product that isn't open-source?
October 20, 2017 at 1:51 pm
It seems you may have a few different things to consider with this. A couple of considerations for this:
So if reports 1, 2, and 3 contain 75% of the total in the queue, they would get 75% of the threads, not 100%.
You can affect the number of threads through MaxQueueThreads or by adding/removing CPUs. You can affect what services are implemented or ignored by SSRS (those threads) using the configuration file and changing the yes/no values for things like IsSchedulingService, IsNotificationService, IsEventService. But you really can't control what percentage of threads are doing what as that is something in Microsoft's code.
Another thing with that would be the "queue" and the size. Since it's not really a queue per se but rather a piece in the flow for the process, nothing in those tables knows anything about the totals or percentages or whatever else is in those tables. It is also dynamic so I'm not sure how you would determine something like 75% as you need to ask at which time or at what point. This looks like it would require that every subscription be fired at the same time if you wanted to prioritize the subscriptions based on all of the subscriptions.
Another consideration would be in regards to:
manipulate the Notifications or Events tables (or the procs that actually pick the work) to prioritize them
The processessing of subscriptions is not done only through the stored procedures. It also involves the methods for the web service which you won't be able to modify.
Modifying the tables really isn't an alternative due to how much will break. You can affect the events table to some degree by calling AddEvent or starting the subscription job - those just put the row in the event table for the given subscription.
There are third party apps that manage subscriptions that you may be able to utilize.You could also look at writing your own processes for managing the subscriptions using the Web service, SOAP API. That's essentially what the third party applications do.
Sue
October 24, 2017 at 8:32 am
yes we've already adjusted CPU count and max queue threads settings while measuring performance data. i'm looking specifically to not have a few reports with a large number of subscriptions bottleneck the whole flow.
granted monkeying with back-end tables in the SSRS database is a no-no... but i can't find a description of the ProcessAfter column in the Notifications table. If I were to set a future date/time in this column (which is currently null in all cases) would this record then not get picked/processed until after that time? if that is the case then this would appear to be the path to the solution i'm looking for.
October 24, 2017 at 10:50 am
agerard 65681 - Tuesday, October 24, 2017 8:32 AMyes we've already adjusted CPU count and max queue threads settings while measuring performance data. i'm looking specifically to not have a few reports with a large number of subscriptions bottleneck the whole flow.granted monkeying with back-end tables in the SSRS database is a no-no... but i can't find a description of the ProcessAfter column in the Notifications table. If I were to set a future date/time in this column (which is currently null in all cases) would this record then not get picked/processed until after that time? if that is the case then this would appear to be the path to the solution i'm looking for.
I think ProcessAfter may not be correct. According to this blog, the order is based on NotificationEntered which makes sense.
SSRS Subscriptions (What goes on “under the hoodâ€)
NotificationEntered does not allow nulls. ProcessAfter column allows nulls.
There is also an Attempt column which also allows null. I would guess ProcessAfter and Attempt combined work with the MaxRetries and SecondsBeforeRetry settings for the different extensions in the configuration file. With the processing being FIFO, it makes sense if NotificationEntered is really what controls the order as indicated in the blog in the link I posted.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply