Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series
Authors: Denny Lee, Lukasz Pawlowski
Contributors: John Gallardo, Robert Bruckner, Dean Kalanquin
Technical Reviewers: Burzin Patel
Introduction
This technical note is part of the Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series
which provides general guidance on how to set up, implement, and
optimize an enterprise scale-out architecture for your Microsoft® SQL
Server® Reporting Services environment. This note provides guidance for
both SQL Server 2005 and 2008 Reporting Services. The focus of this
technical note is Scale-Out Deployment Best Practices – the Reporting
Services Windows®/Web Services servers that provide your reports.
Architecture
Figure 1 shows a typical scale-out Reporting Services
environment; as noted by the red box, the focus of this technical note
is scale-out deployment.
Figure 1: Reporting Services scale-out architecture
Report Catalog Sizing
As noted in the previous technical note of this series, Report Server Catalog Best Practices,
the report server catalogs are an integral part of your Reporting
Services solution. When you begin the process of building your
Reporting Services scale-out deployment, you will need to appropriately
scale your report server catalogs to handle the additional workload. In
addition to the concepts discussed in the aforementioned technical
note, a key consideration is Report Catalog sizing; the two report
server catalogs (RSDB and RSTempDB) have different sizing requirements.
Report Server (RSDB) Database Size
The size of this database typically varies by the
number of reports published and the number of history snapshots
associated with each report. A general rule of thumb is that a
moderate-sized report definition will take about 100-200 KB of disk
space, which is larger than the actual size of the RDL. This is because
Reporting Services will persist both the RDL and the compiled binary
representation to improve report execution performance. In the case of
history snapshot sizes, this is a function of the amount of data that
is in the report (i.e., the larger your report and datasets, the larger
your snapshot). A general rule of thumb is that Reporting Services has
an approximate 5:1 compression ratio; therefore if your report has 10
MB of data, then the snapshot will be about 2 MB in size. Note that
reports that do not perform aggregations will be slightly larger than
reports that do.
Report Server TempDB (RSTempDB) Database Size
The size of this database varies depending on the
number of users who are concurrently using the report servers. Each
live report execution generates a report snapshot that is persisted in
the RSTempDB for the duration of the user’s session. Because of
the high degree of variability of the number of users who are accessing
your report servers at any one time, your sizing estimate should be
based on the maximum number of concurrent users that access your
Reporting Services environment. A general rule of thumb here is that
you will typically see a maximum of 10-20% concurrency of your user
base.
For example, if you have 1,000 users, then you
can have up to 200 concurrent users. If most of these users are
accessing your 10-MB report, then you will need to have at least 400 MB
of storage when taking compression into account. Of course, as users
are no longer querying for reports and/or report sessions are timing
out, the space will be reclaimed and made available for new users. But
at the same time, to size properly, you will want to make your
calculation based on the maximum number of concurrent users.
Enable File System Snapshots for SQL Server 2005 Reporting Services
Please note that this is applicable for SQL Server 2005
Reporting Services, not SQL Server 2008 SQL Server 2008 Reporting
Services. As noted in Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned, this feature did not provide the scale of performance benefit (more on this later) for SQL Server 2008 Reporting Services.
Recall that the report server uses snapshot data stored in RSTempDB to render reports. Therefore RSTempDB
gets filled up fast, because there are many transactions performed to
keep report consistency (e.g., if I get the first page of a report, we
need a snapshot of the second page that is taken from the same data as
the first). Because of this concern for consistency, it is then
important for Reporting Services to cache all of this information. To
reduce the number of transactions being placed on your RSTempDB,
use the File System (FS) snapshots feature instead. With it enabled,
snapshot data persists on the local Reporting Services server.
Therefore, this reduces the network traffic from the Reporting Services
server to the catalog, which also reduces the number and size of
transactions within the catalog. For more information about this
feature, see the Planning for Scalability and Performance with Reporting Services white paper.
For Reporting Services to efficiently access this
snapshot data, it is spread over a number of chunks – logical divisions
of smaller size. During regular operations, the report server may need
to access the chunks multiple times to satisfy user requests. For
example, a particular chunk may have been removed from server memory,
so the report server needs to fetch it again from RSTempDB. By default, the server must query RSTempDB
each time it needs to get a snapshot chunk. As user load increases due
to report complexity or concurrency, the number of transactions to RSTempDB also increases. This can cause performance degradation on RSTempDB.
To mitigate this, Reporting Services provides configuration options to
create file system chunks (i.e., file system snapshots), which act as a
cache for the snapshot chunks on the node that created the chunk. This
reduction in load on RSTempDB allows your Report Catalog to scale better.
Now that the report data is stored on the file
system, it is a requirement to use a load balancing solution to
affinitize user sessions to their report server node (more on this
later). The slight drawback to this feature is that if you were to lose
a reporting server, you would also lose the file system data cache that
corresponds to that reporting server. However, the metadata is still
available in RSDB and the original data is in the reporting
data. Your users would simply connect to a different report server and
make a new request for this report, experiencing slower initial query
performance before the file system snapshots kick in.
Therefore, for large-scale SQL Server 2005
Reporting Services deployments, we recommend you turn on the file
system storage of snapshot chunks. To do that, you must set WebServiceUseFileShareStorage and WindowsServiceUseFileShareStorage to True. You can control the location of the files using the FileShareStorageLocation configuration
property. These settings are available in the file
rsreportserver.config located under the report server installation
folder.
<Add Key="WebServiceUseFileShareStorage" Value="true" /><WindowsServiceUseFileShareStorage>True</WindowsServiceUseFileShareStorage>
Note that if you enable this feature, it will require
more disk space than your standard Internet Information Services (IIS)
configuration, because of the large amount of data that will be written
onto disk. (For more information, see the Report Catalog sizing
information in the previous section.)
Why Not File System Snapshots for SQL Server 2008 Reporting Services?
There are a number of changes and optimizations to SQL
Server 2008 Reporting Services, including better utilization of memory
and of the file system. Enabling file system snapshots in SQL Server
2005 Reporting Services was an advantage because unless this feature
was enabled, a majority of the transactions required hitting the RSTempDB.
But SQL Server 2008 Reporting Services caches a lot of this data into
memory. Meanwhile the data is continually persisted to the Report
Catalogs so that the local file system acts as a write-through cache,
regardless of whether file system snapshots are enabled. This was done
to ensure stability in case of incorrect load balancing affinity or
gaps (e.g., secondary output streams such as CSS style sheets in HTML,
report images retrieved in HTML) in report snapshots.
The design in SQL Server 2005 Reporting Services
was that it generally would not materialize the original datasets in
the report snapshot, except for particular cases. This was possible
since the entire report was calculated in SQL Server 2005 Reporting
Services when the first page was requested, and the report contents
were persisted in a output-format independent way. On the other hand,
SQL Server 2008 Reporting Services does not precalculate everything on
the initial request – therefore, it has to always serialize all
datasets so that it can calculate report contents as needed later.
There are still certain parts in SQL Server 2008 Reporting Services
that once calculated (e.g., group/sort/filter/aggregates) are persisted
and not recalculated. However, many parts of a report (e.g., text box
values, style values) are calculated on demand instead of precalculated
and stored.
Another reason file system snapshots do not have
as profound impact for SQL Server 2008 Reporting Services is that when
reports are executed, the Reporting Services on-demand engine will
initially retrieve all of the data for your report and write this data
into the RSTempDB, to achieve a consistent data snapshot. It
will then do the calculations and groups required for the first page.
But subsequent calls are more efficient and have much less impact than
SQL Server 2005 Reporting Services (e.g., page 2 of the report) because
the on-demand engine will only access a subset of the data instead of
requesting for the entire dataset. Note that the data is materialized
in the initial request (e.g., render first page) when all dataset
requirements are executed. Later session requests (e.g., navigate to
second page, export to Microsoft Excel®) will use the data already
materialized in the session snapshot.
This observation was also noted in the Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned
technical note. Saying this, you may still want to test the efficacy of
this feature within your environment, because there could be variables
that allow this feature to help improve performance – but it certainly
doesn’t always have benefits as it did for SQL Server 2005 Reporting
Services.
Turn on Cache Execution
As you may have noticed, so far we have a recurring
theme concerning the effective use of memory and desire to have minimal
impact on the file system. After all, a key component for scalability
is the ability to reduce I/O. Of course, CPU and memory are extremely
important for scalability, but often it is the disk I/O that is slowing
down your performance. A powerful feature to reduce I/O is to enable
the cache execution feature of your reports. By default, when you
deploy your Reporting Services reports, it will be a live execution
report – i.e., upon report execution, Reporting Services will query the
snapshot or your data source. At a minimum, this means that for every
report execution some I/O occurs to provide your users with data. By
using cache execution, Reporting Services caches a temporary copy of
this report into memory that expires after a set number of minutes.
Report executions during this time period request data from memory
instead of disk, thus reducing I/O. Even if you set your cache timeout
for your report to every five minutes, you will still see an 80%
reduction in I/O for your report.
To configure cache execution, after you deploy
your reports to your report server, you will need to configure each
report (i.e., there is no global setting) to cache a temporary copy of
the report. Note that you will want to set your reports to use stored
credentials instead of Windows integrated security, in order for the
cache execution to work effectively. Do not forget to set up your
Reporting Services keys to ensure encryption of the stored credentials.
Load Balance Your Network
It is very important to load balance your many client
connections to your Reporting Services servers. We recommend the use of
a hardware network load balancer (NLB) that allows your client
connections to balance across your Reporting Services servers, as noted
in Figure 2.
Figure 2: Load balancing your network
We also recommend that you use cookie persistence
(refer to your NLB guide to set this up) to preserve the
client-to-Reporting Services server connection – i.e., to affinitize
the client to the Reporting Services server. You can consider IP
affinity, but this can overload under browser-based or proxy
connections. Note that you typically will round-robin the initial
connections to minimize the load on your NLB. Subsequent queries will
have affinity to allow the clients to make use of the Reporting
Services local file cache.
Because your Reporting Services servers are
making heavy use of the network, you may want to consider also placing
additional NIC cards for your Reporting Services servers. In enterprise
Reporting Services environments, an important task will be to profile
your network resources so you can determine where your network resource
bottleneck is. If you are running into network bottlenecks, a setup to
consider if you want to scale up your network load is to have two NIC
cards, one for the browser traffic and one for the database traffic.
Isolate Your Workloads
Within your enterprise Reporting Services environment,
you will typically have two types of users – one set that assesses
scheduled reports and another set that reviews the reports in an
interactive manner. A common profile differentiation between these two
types of reports is that users accessing interactive reports will
execute their reports often in order to drill up, drill down, filter,
etc. On the other hand, scheduled reports typically will process
against much larger sources of data (which involves a considerable hit
on the system to produce the reports; hence scheduling will reduce the
load) or have a much larger audience (where the load created by the
concurrency of a large number of users to query the same report can be
reduced by creating the report beforehand). Whether you are dealing
with interactive users or scheduled report users, you have a situation
where your users produce two different types of profiles as noted in
Figure 3.
Figure 3: Isolating your workloads
Therefore, it is suggested that within your
scale-out deployment, you also isolate your workloads so that you have
one set of servers devoted to working with your interactive users (Interaction) while another set of servers work on scheduled reports (Scheduling).
As implied in Figure 3, doing so will provide you with a more
predictable workload because of the different resource profile that is
used by these different report users. Workload isolation will also
allow you to isolate any performance issues associated with these two
types of reporting users; that way, interactive users and scheduled
report users are not interfering with each other.
Note that ad-hoc report users (i.e., users who
use Report Builder) are a different class of interactive users. Because
these users can build whatever reports desired, there is a high degree
of unpredictability in the amount of resources that this class of users
will require. In extreme cases, you may want to consider creating a
completely separate deployment for these ad-hoc users so that they do
not bottleneck your interactive or scheduled report users.
To isolate your workloads, set your scheduling Reporting Services servers to Schedule Events and Report Delivery Enabled, and set your interactive Reporting Services servers to Web Service and HTTP Access Enabled.
By default both options are enabled, so make sure only one is enabled
on each server when you decide workload isolation is the path you will
take.
Additional Notes on Workload Isolation
You can also make these workload isolation changes
programmatically through WMI using the Reporting Services WMI Provider
Library. You will change the SetServiceState method; for more information, see the SQL Server Books Online > SetServiceState Method.
To help isolate your reporting bottlenecks for scheduled reports (the Scheduled Events and Report Delivery), schedule your reports so they are not being processed in parallel. This way you will be able to isolate performance issues.
Report Data Performance Considerations
With all of these configuration and infrastructure
considerations, you will be able to build an optimal scale-out
environment for your Reporting Services solution. But while your
Reporting Services environment is scaled out, do not forget the impact
on your underlying reporting data – i.e., the data source. Here are
some of the things you can do to reduce the impact on your report data:
- Limit the dataset size by using query filters.
- If you need to provide large dataset sizes, use
alternate mechanisms such as Integration Services to provide an extract
that is accessible from the file system, instead of providing a
million-page report.
- Limit the number of reports that users can
access. This is not just a security concern. Limiting access reduces
your maintenance cost by preventing users from creating multiple
similar subscriptions and reports.
But even after making the above changes, you may still
find yourself placing far too much load on your reporting data. To help
resolve these issues, you may want to consider also scaling out your
underlying data sources. Figure 4 is an example of a scale-out solution
where a set of two Reporting Services servers are assigned to a single
Analysis Services server. The underlying reporting data (in this case
Analysis Services cubes) is replicated to multiple Analysis Services
query servers so that every two Reporting Services servers query only a
single Analysis Services instance. Note that to do this you will need
to manually create local data source aliases on the reporting servers
that will perform this type of server assignment.
Figure 4: Scaling out Analysis Services
These options are outside the scope of a Reporting
Services specific technical note, but here are some resources that you
may find helpful:
- Deploying a Scalable Shared Database:
This feature allows you to deploy multiple read-only versions of your
SQL Server database for the purposes of reporting. Scalable shared
databases offer a scale-out solution in which multiple servers hold
read-only copies of your SQL Server database that users can query thus
distributing the query load across multiple databases or servers.
Next Steps…
Now, that you’ve scaled out your Reporting Services
deployment – there are additional features and configurations that may
optimize your Reporting Services environment; for more information, see
SSRS Performance Optimization Configurations [link provided when published].
