Report Server Catalog Best Practices
As of September 1, 2013 we decided to remove SQLCAT.COM site and use MSDN as the primary vehicle to post new SQL Server content. This was done to minimize reader confusion and to streamline content publication. MSDN SQLCAT blogs already includes most SQLCAT.COM Content and will continue to be updated with more SQLCAT learnings. You can also find a collection of our work in SQLCAT Guidance eBooks.
To make the transition to MSDN smoother, we are in the process of reposting a few of SQLCAT.COM’s blogs that are still being very actively viewed. Following is reposting of one of the SQLCAT.Com blogs that still draws huge interest. You can find this, and other guidance in the SQLCAT's Guide to BI and Analytics eBook.
Also, Follow us on Twitter as we normally use our Twitter handles @MSSQLCAT and @MSAzureCAT to announce news and new content.
Report Server Catalog Best Practices
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 (SSRS) environment. This note provides guidance for both SQL Server 2005 and 2008 Reporting Services. The focus of this technical note is the report server catalog—the underlying databases that provide the metadata (parameters, snapshots, history, and so on) that are used by Reporting Services to provide your reports.
Architecture
Figure 1 represents a typical scale-out Reporting Services environment; as indicated by the red box, the focus of this technical note is that of the report server catalog.
As you can see, all Reporting Services servers have connections to the report server catalog. As implied by this diagram, this single instance could be a possible bottleneck in the SSRS environment. Because of this, let’s focus on what these report server databases do and how to optimize them for your environment.
Report Server Databases
The report server catalog is comprised of two report server databases:
ReportServer
(RSDB) stores all report metadata including report definitions, report history and snapshots, and scheduling information.
ReportServerTempDB
(RSTempDB) stores all of the temporary snapshots while reports are running.
Figure 2: Breakdown of Report Server Catalog
These databases are responsible for containing the metadata that is needed by the Reporting Services engine so that it knows how to request data from the data source (such as a SQL Server database or an Analysis Services database). When a report is executed by a report server, SSRS loads report metadata from RSDB. This includes the data source information for the report, the default parameter values, and the report itself. Note that for ad-hoc reports, this data is not loaded from the RSDB catalog database because the report comes from the RDL that is executed, published, and bound to the user’s session. As well, all user session information and live or cached execution snapshots are stored in the RSTempDB database. History and execution snapshots are stored in the RSDB database. Subsequent report requests can use the stored state to view the report rather than re-executing the report.
Report execution state is represented in a snapshot. The snapshot contains the data that is retrieved from the report’s queries to the data source. SSRS keeps these snapshots so that subsequent requests for the same report can be satisfied by using the snapshot instead of re-executing the report and obtaining the data again from the original data source (thus reducing the load on your data source). By default, these snapshots are stored in the RSTempDB database. Therefore, commonly requested reports hit the RSTempDB database instead of querying the original data source (provided the snapshot is current). While this reduces the load against your original data source, it does increase the load on your SSRS catalog. The size of your snapshot is directly correlated to the size of the data returned by the queries of your reports. Because of this, it is important to reduce the size of your snapshots by including only the columns (and rows) you need.
In addition, this also implies that you must optimize the SSRS report server catalog to prevent it from being a bottleneck since every single report request hits RSDB. For frequently requested reports, many database queries will hit RSTempDB. Because these databases are hit for every single report request, the report server catalog has a lot of I/O and transactions in order to share state information across multiple SSRS servers. The RSTempDB tables and log will grow quickly because many transactions are performed to maintain report consistency. (For example, if you get the first page of a report, you need a snapshot of the second page that is consistent.) If you have an environment with many concurrent requests (typical of most enterprise Reporting Services environments), there can be a lot of write activity to the RSTempDB log.
For SQL Server 2005 Reporting Services, many of the insertions are performed to the ChunkData, SnapshotData, and SessionData tables. But SQL Server 2008 in general does not write to these tables. They exist in the catalog to support the upgrade of pre-2008 catalog. SQL Server 2008 Reporting Services uses a new snapshot storage mechanism that shreds chunks across multiple rows in a table named Segment. For SQL Server 2008 Reporting Services, this table generally takes on the majority of transactions of the RSTempDB database.
As implied by the above, there are differences in RSDB and RSTempDB access patterns between versions of SSRS. SQL Server 2008 Reporting Services makes use of RSTempDB significantly more than SQL Server 2005 Reporting Services does. This is because we process data incrementally during report execution rather than immediately when the report is executed. To make the report execution reliable, we store the data for the report in RSTempDB until it is needed to generate a particular page of the report. Clearly, this increases the number and size of queries executed against the RSTempDB database and can lead to bottlenecks.
Therefore, to optimize your report server catalog, we suggest the following best practices.
Use a Dedicated Server
As noted earlier, a heavy transaction load is placed on the SSRS report server catalog. Because of this, the SSRS report server catalog load can interfere with other processes and vice versa. For example, common environment setups include the following:
Same server as SSRS Windows/Web Services
The default one-box setup usually has the SSRS Windows/Web Services and the report server catalog on the same server. While this works great for small environments, in enterprise environments it causes far too much resource (CPU, memory, and disk) contention between the SSRS Windows/Web Services and the report server catalog. As well, when you scale out and add more SSRS servers, you want to have all of SSRS servers point to one dedicated report server catalog to reduce contention.
Same server as your data source relational database (SQL)
Another common approach is to place your SSRS report server catalog on the same server as your SQL Server data source. The issue here is that you will have SQL resource contention (tempdb, plan cache, memory, buffer pool, etc.) between your SQL Server data source and your SSRS report server catalog. As you have more concurrent users, you will have a lot of hits to the SSRS report catalog (RSDB for report metadata, RSTempDB for report snapshots) and transactions against the relational data source. As the load increases, it will become necessary to monitor things like CPU utilization, I/O response times, network resources, and buffer pool counters to ensure that adequate resources are still available. A common method to alleviate these issues is to separate the SSRS report server catalog from your SQL server data source.
As you can see, these two common scenarios create database resource contentions that slow down performance. Because of this, it makes sense to have a dedicated server for your SSRS report server catalog so that you can tune your report server databases separately from your relational data source and not run into SSRS Windows/Web Services and report catalog resource contention.
High-Performance Disk
Because your SSRS report server catalog has a lot of transactions, ultimately there will be a lot of disk I/O so that storage may be your resource contention. Because of this, you want to have a high-performance disk such as a SAN or high-RPM direct-attach storage for your report server catalog. Some more specifics:
To optimize your disk I/O, see the SQL Server Best Practices white paper Predeployment I/O Best Practices, which provides great information on how storage works and how to use various tools to understand what performance you may be able to obtain for SQL Server from the storage you have.
Have more smaller sized disks with faster rotation speeds (e.g. >= 15,000 RPMs) rather than fewer larger sized disks with slower rotation speeds. That is, you should size your I/O based on IOS requirements instead of sizing based on capacity.
Maximize and balance your I/O across all of the available spindles.
Use separate disks for RSDB and RSTempDB. The profile for your RSDB is a lot of small transactions because it asks for report metadata. Your RSTempDB will have a lot of transactions as well, but they will be larger transactions because this database contains the report data. Having separate disks will enable you to tune your disk for the RSDB and RSTempDB databases separately.
Pre-grow your SSRS report server catalog databases instead of having SQL Server perform autogrow on these databases. RSTempDB can grow very quickly under load and the default autogrow options lead to a lot of file system fragmentation and blocking during the autogrow task.
For your SSRS server catalog databases, stripe your database files to the number of server cores at a ratio of 0.25 to 1.0 depending on how heavy your workload is. This enables you to minimize database allocation contention and makes it easier to rebalance your database when new LUNs become available.
If you are using RAID, like many other SQL implementations that are write-intensive, use RAID 10 to get the best performance. Do not use RAID 5 because of the write penalty that may be involved.
Monitor disk response times to ensure that disk latency is generally lower than 20ms, ideally 10ms, and log latency is no more than 1-5ms. To do this, look for high wait times on PAGEIOLATCH_xx or use sys.dm_os_virtual_file_stats to monitor response times specifically on SSRS-related databases.
Move to 64-bit
For starters, if you need to stay with 32-bit and have >3 GB of memory because of the available hardware and OS, remember to use the /3GB and/or the /PAE (for systems with >4 GB of memory) switches (for the OS) and enable AWE in SQL Server so that it can use more than 3 GB of memory. Note that AWE can only be used for data cache. Do not forget that this involves both SQL Server changes (configure advanced options) and changes to the Windows OS boot.ini file.
We suggest moving to 64-bit because much of the hardware available right now is 64-bit and, as of SQL Server 2005, SQL Server itself natively supports 64-bit. With 64-bit, you have a much larger addressable memory space to use—especially if you increase the amount of memory. This means that you can handle larger queries (more data) and handle more connections to the server running SQL Server. Note that this does not result in higher throughput as that is typically bound to CPU. Nevertheless, the ability to handle more connections and larger reports minimize the chance that your report server catalog will be a bottleneck for your system. As well, the ability for 64-bit to scale is much higher than 32-bit and this is the platform of choice for SQL databases going forward.
Backup/Restore
The data in RSTempDB is highly volatile—typically one can expect its lifespan to be approximately equal to the SessionTimeout value configured for the SSRS server for most reports and viewing and usage time. The default SessionTimeout is 10 minutes, which is the report lifetime policy that defines when data can be cleaned up. The CleanupCycleMinutes value is the parameter that guides the background cleanup thread. Once the session timeout value is reached, we clean up the temporary snapshot from tempdb. We do that every cleanup cycle minutes, or continuously if the previous cleanup didn’t complete yet. The actual lifespan varies based on usage patterns but a lifespan longer than one day would be rare. As such, it is not necessary to protect RSTempDB data for data recovery purposes.
The data in RSDB is long lived—this data should be backed up following the standard guidance provided for SQL Server:
Backing Up and Restoring Databases in SQL Server
Optimizing Backup and Restore Performance in SQL Server
As well, do not forget to back up (and restore) the encryption key associated with these databases; you can find more information at Backing Up and Restoring Encryption Keys
Maintain Your SSRS Report Server Catalog Databases
Recall that the SSRS report server catalog databases are SQL Server databases specifically for Reporting Services usage. Therefore, the standard techniques to maintain SQL databases apply to the SSRS report server catalog databases. For example, periodically re-indexing the catalog tables and/or updating the database statistics may improve query performance.
As noted above, you may want to consider configuring the CleanupCycleMinutes setting in the RSReportServer.config file. This setting determines how frequently expired session content or unused snapshot data is removed from RSTempDB. The default setting is 10 minutes, which is similar to the default session timeout. RSTempDB generally stays more compact when using frequent cleanups, but at the cost of increasing the general load on the system. If the size of RSTempDB not a major concern and the system has high throughput user loads, you may want to considering slightly increasing the CleanupCycleMinutes configuration (such as setting it to 20 minutes).
Discussion
Since the SSRS Windows/Web Services interact with your report server catalog for almost all SSRS queries, it is important that you optimize your SSRS catalog databases so that they are not a point of contention. Standard SQL optimization techniques come into play here since SSRS report server catalogs are instances of SQL Server databases. Following the above suggested methods will make your SSRS environment easier to scale to enterprise data loads.
Recall that because of the concern for report consistency, it is important for Reporting Services to cache all report data. Therefore, to reduce the number of requests that are placed on your report server catalog, you may want to consider using the File System (FS) Snapshots feature, which is discussed in the next technical note of this technical series—SSRS Scale-Out Deployment Best Practices [work in progress].