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.
