SSRS Subscriptions (What goes on “under the hood”)

  • Article

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.

  • The Event table will either hold a TimedSubscription event or a DataDrivenSubscription event. Doing a simple SELECT statement and looking at the EventType can show you if and what type of records are queueing up in there (if any).

  • The Notifications table will have a single Notification record for a standard subscription (with an IsDataDriven value of 0) or potentially multiple Notification records for a data driven subscription (with an IsDataDriven value of 1). Most frequently when SSRS subscriptions get queued up I see the bottleneck in the Notifications table. You can use the following query to look at the queue inside the Notifications table to see it in the exact order that SSRS will process subscriptions:

    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 

  • The ActiveSubscriptions table is used only for data driven subscriptions. This is where we'll keep track of how many Notifications for each data driven have succeeded or failed (which is why in the LastStatus column of the Subscriptions table for a data driven subscription will always say something like "Done: X Processed of Y total: Z errors". The values for X, Y, and Z are pulled from the ActiveSubscriptions table.

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.

Comments

  • Anonymous
    July 04, 2016
    CREATE PROCEDURE dbo.StartupNotificationAS DECLARE @Subj NVARCHAR(255) DECLARE @MailBody NVARCHAR(MAX) DECLARE @Restart NVARCHAR(MAX) SET @Subj = @@SERVERNAME + ' - SQL Server service restart' SELECT @Restart = CONVERT(NVARCHAR, sqlserver_start_time, 109) FROM sys.dm_os_sys_info SELECT @MailBody = 'The SQL Server service for the following was (re)started.' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + 'SQL Server: ' + @@SERVERNAME + CHAR(13) + CHAR(10) + 'Last Restart: ' + @Restart EXEC msdb.dbo.sp_send_dbmail @recipients = 'Dave@Celtics.com', @Subject = @Subj, @body = @MailBodyGOEXEC sp_procoption @ProcName = 'dbo.StartupNotification', @OptionName = 'startup', @OptionValue = 'true'
    • Anonymous
      July 04, 2016
      Go
  • Anonymous
    July 04, 2016
    sp_executesql [ @stmt = ] statement [ { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } { , [ @param1 = ] 'value1' [ ,...n ] } ]