http://www.sqlservercentral.com/blogs/pturley/2010/12/15/preventing-subscriptions-from-blocking-interactive-report-executions/

Printed 2014/12/21 02:16PM

Preventing Subscriptions from Blocking Interactive Report Executions

By pturley, 2010/12/15

A consulting client recently asked how they could resolve a capacity-planning issue on their production report server.  They manage a busy report server for a 24/7 international operation.  There are several long-running subscription reports scheduled to run during busy times of the day when other users need to run reports on-demand.  When some of the subscriptions run, the server slows down and users don’t get very good response for their interactive reports.  The server is currently a two node server farm that has plenty of capacity and performance is good with the exception of these infrequent slow down periods.

One thought was to shut off subscription support all together on one of the two report servers but this proved to be too restrictive.  The solution was to limit the number of subscriptions that could run on one of the two servers.  This was done using the MaxQueueThreads setting in the RsReportServer.config file.  This allows allows you to set a maximum limit on the number of process threads the report server will dedicate to subscriptions and notifications. You could either set this to a low number (like 2 or 3) to allow minimal subscription processing on the cluster node or just shut subscriptions off all together. You can disable subscriptions and other non-critical features on the node using IsSchedulingService, IsNotificationService and IsEventService. These settings are documented at http://msdn.microsoft.com/en-us/library/ms157273.aspx

So on report server 1, we modify the RsReportServer.config:


<Service>
    <IsSchedulingService>True</IsSchedulingService>
    <IsNotificationService>True</IsNotificationService>
    <IsEventService>True</IsEventService>
    <PollingInterval>10</PollingInterval>
    <WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
    <MemorySafetyMargin>80</MemorySafetyMargin>
    <MemoryThreshold>90</MemoryThreshold>
    <RecycleTime>720</RecycleTime>
    <MaxAppDomainUnloadTime>30</MaxAppDomainUnloadTime>
    <MaxQueueThreads>4</MaxQueueThreads>
    <UrlRoot>
    </UrlRoot>
    <UnattendedExecutionAccount>
        <UserName></UserName>
        <Password></Password>
        <Domain></Domain>
    </UnattendedExecutionAccount>
    <PolicyLevel>rssrvpolicy.config</PolicyLevel>
    <IsWebServiceEnabled>True</IsWebServiceEnabled>
    <IsReportManagerEnabled>True</IsReportManagerEnabled>
    <FileShareStorageLocation>
        <Path>
        </Path>
    </FileShareStorageLocation>
</Service>

…and on report server 2, we leave the default setting:


<Service>
    <IsSchedulingService>True</IsSchedulingService>
    <IsNotificationService>True</IsNotificationService>
    <IsEventService>True</IsEventService>
    <PollingInterval>10</PollingInterval>
    <WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
    <MemorySafetyMargin>80</MemorySafetyMargin>
    <MemoryThreshold>90</MemoryThreshold>
    <RecycleTime>720</RecycleTime>
    <MaxAppDomainUnloadTime>30</MaxAppDomainUnloadTime>
   <MaxQueueThreads>0</MaxQueueThreads>
    <UrlRoot>
    </UrlRoot>
    <UnattendedExecutionAccount>
        <UserName></UserName>
        <Password></Password>
        <Domain></Domain>
    </UnattendedExecutionAccount>
    <PolicyLevel>rssrvpolicy.config</PolicyLevel>
    <IsWebServiceEnabled>True</IsWebServiceEnabled>
    <IsReportManagerEnabled>True</IsReportManagerEnabled>
    <FileShareStorageLocation>
        <Path>
        </Path>
    </FileShareStorageLocation>
</Service>

The default setting of zero gives the server some flexibility to balance this on its own.  During busy on-demand cycles (when users are running their own reports interactively), subscriptions will still run on the other server but will use limited resources on the throttled server.  We can also schedule a command script to change this setting at different times of the day so more threads can be used for subscriptions during off hours.  We haven’t found this option necessary.



Weblog by Paul Turley and SQL Server BI Blog.
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.