First published on MSDN on Jun 20, 2016
One of the more difficult features to understand/troubleshoot that I've found in my experiences working with SSRS customers is the SSRS subscription functionality, especially the subscription processing piece of it before the report execution starts. Once we get to the report execution it's fairly easy to figure out what's happening or not happening from there but until then all we see in the SSRS trace logs is a series of Event/Notification messages pertaining to a particular subscription and not something that gives us an all up view of what's happening overall on in SSRS.
To help assist with this I put together the flow chart below that shows the process a subscription goes through, whether it's a standard subscription or a data driven subscription, inside of the Reporting Services catalog database (typically named ReportServer). Understanding this flow can be useful in determining if you have subscription events or notifications getting queued up in your database prior to the report execution even being attempted.
As you can see the primary tables associated with subscription processing are the Event, Notifications, and ActiveSubscriptions table.
SELECT n.SubscriptionID ,c.Name AS ReportName ,c.Path AS ReportPath ,u.UserName AS SubscriptionOwner ,n.ExtensionSettings ,n.NotificationEntered AS QueuedSinceTime ,n.ProcessAfter ,n.SubscriptionLastRunTime ,n.DeliveryExtension FROM dbo.Notifications n WITH (NOLOCK) INNER JOIN dbo.CATALOG c WITH (NOLOCK) ON n.ReportID = c.ItemID INNER JOIN dbo.Users u WITH (NOLOCK) ON n.SubscriptionOwnerID = u.UserID WHERE n.ProcessStart IS NULL AND ( n.ProcessAfter IS NULL OR n.ProcessAfter < GETUTCDATE() ) ORDER BY n.NotificationEntered
You'll also notice from the flow chart that the real "engine" behind subscription processing is the dedicated subscription threads within the ReportingServicesService.exe process. When bottlenecks occur it may not be that we have too many subscriptions trying to process at once, it could be because our subscription threads are all tied up executing long running reports/subscriptions and we've hit the maximum number of threads we can spawn for subscription processing. We can configure this by modifying the MaxQueueThreads value inside of our RsReportServer.config Configuration File . The default for this value is 0 which means it'll be determined by the number of CPUs on the Report Server (it is usually two times the number of logical CPUs on a machine). It may be beneficial to turn this value up if subscriptions are getting queued up but the resources on the Report Server (and SQL Server hosting the SSRS databases) are not being taxed.
I've found in researching the web there is little to no documentation out there for these particular tables. While that is partially by design because we don't want you making any modifications to records within these tables it also ends up clouding up our understanding of how those tables relate to subscription processing. Understanding how a subscription queue bottleneck happened and how to identify it are one in the same and can be accomplished by understanding the flow "under the hood" of SSRS subscriptions and looking at the pertinent tables that give an idea of what the subscription queue looks like at any given point.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.