Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Preventing Subscriptions from Blocking Interactive Report Executions

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.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.