Tim Radney

Tuning SQL Server Reporting Services

September 17, 2019 by in Reporting Services, SQL Performance | No Comments
Database smarter with deeper insights for Microsoft SQL
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe →

Featured Author

Paul Randal, CEO of SQLskills, writes about knee-jerk performance tuning, DBCC, and SQL Server internals.

Paul’s Posts →

Many database administrators find themselves having to support instances of SQL Server Reporting Services (SSRS), or at least the backend databases that are required for SSRS. For years SSRS was bundled with the installation of SQL Server, which helped add to some of the confusion around licensing and support for the product, so beginning with SSRS 2017, the installation package for Reporting Services is a separate download.

This article will cover many areas that database administrators need to be aware of in order to properly license, recover, and tune a Reporting Services installation. These topics apply to both SQL Server Reporting Services as well as Power BI Report Server.

Licensing

Installation and support of SSRS can be confusing. The reporting service can be installed as a standalone instance on a dedicated server, on the same instance as SQL Server, or in a scale-out deployment (Enterprise Edition only). Each instance where SSRS is installed in production requires a SQL Server license, as well as licensing the instance of SQL Server where the ReportServer and ReportServerTempDB databases reside.

The way I like to explain how to license Reporting Services is to think about the reporting service as an application that uses SQL Server on the back end. In the early days of SSRS, a requirement was to also install and configure Internet Information Services (IIS). SSRS 2008 brought that component into the reporting service module. It is very common to see SSRS and MSSQL installed on the same instance due to licensing and this can work well for smaller implementations. For larger deployments, it’s common to see a dedicated reporting service server with the ReportServer and ReportServerTempDB on a consolidated SQL Server. For very large installations, scale-out deployments are used to provide load balancing of the reporting server service. In a scale-out deployment, each instance in the farm must be licensed.

Recovery

In each of the deployment models, the role of the database administrator is to make sure that SSRS is stable, dependable, and recoverable. The recoverable part is something that causes some DBAs issues. The ReportServer database is encrypted and certain operations require restoring the symmetric key. If there is a failure and the database has to be restored to another server, the Report Server Windows service account name or password is changed, the computer name is changed, you migrate to another server, or you add a new server to a scale-out configuration, you’ll be required to restore the encryption key. Unless the key is backed up, any protected data, such as connection strings and passwords, can’t be decrypted. I’ve found that many DBAs are unaware of this until it’s too late. The key can be backed up and restored manually using the Reporting Services Configuration Manager, using the rskeymgmt.exe utility, or using PowerShell. You technically only need to back up one copy of the symmetric key.

The ReportServer and ReportServerTempDB databases are SQL Server databases and should be part of a regular backup process, just like other user databases. ReportServer should be using the full recovery model whereas the ReportServerTempDB can be using the simple recovery model. Technically, ReportServerTempDB can be recreated by a script in the event of a disaster, however Reporting Services cannot start without ReportServerTempDB. DBAs are familiar with restoring databases, rather than hunting for a script to recreate the database. Unlike the system database tempdb, ReportServerTempDB is not recreated at startup. Best practice for DBAs is really to just treat ReportServer and ReportServerTempDB like any other user database.

There are configuration files that store application settings that should also be backed up. These may be covered by your OS-level backups; however, DBAs should make sure these files are backed up after the initial configuration and/or after any custom extensions are applied. These files consist of:

  • Rsreportserver.config
  • Rssvrpolicy.config
  • Rsmgrpolicy.config
  • Reportingservciesservice.exe.config
  • Web.config
  • Machine.config

Consideration for backing up your Report Designer files such as; .rdl, .rds, .dv, .ds, rptproj, and .sln files should be given.

Tuning Options

Tuning SSRS is much like any other application. Users are executing reports from an application server that is communicating with databases. The big difference is that you have an application server (Reporting Services) with its own databases, but the report has data sources connecting to other user databases. DBAs must tune for overall health of the Reporting Services infrastructure as well as tuning the actual reports.

Reporting Services Infrastructure

Disk latency for ReportServer and ReportServerTempDB are very important. Depending on the workload, those databases may need to be placed on faster disks. Caches of report results are stored in the ReportServerTempDB database and I/O performance can become an issue here.

The Reporting Services workload may dictate that additional Reporting Services instances are needed to handle that workload. A scale-out deployment requires an Enterprise Edition license. Benefits of a scale-out deployment include giving customers load balancing for higher throughput, high availability, as well as the ability to segment report server processing.

Take advantage of Snapshots where they make sense. If you have reports that are using day-old data, consider using a Snapshot so that subsequent views of that report are using a Snapshot rather than having to generate the entire report again.

Data Driven Subscriptions can be used to run reports and deliver the content based on subscriber base and on a schedule. Based on the schedule, many of these reports can be run after data processing is complete, well before users arrive at work, in a less busy time for the environment.

DBAs should be familiar with execution logging as that can help identify reports that could be candidates for caching as well as reports that should be looked at for performance improvement based on execution processing. Execution logging provides insights into things like how often reports are run, the most requested format, and the percentage of processing tied to a phase of the report process. This data can be accessed using the built-in views for ExecutionLog, ExecutionLog2, and ExecutionLog3.

General Tuning

For the user databases being used by the reports and the instance holding the ReportServer and ReportServerTempDB, you want to track and baseline performance. You need to be monitoring memory/disk/CPU utilization, network I/O, tempdb usage, waits, and other counters to know what is normal for the overall workload of those systems. If users start reporting problems, you need to be able to know if the current utilization is normal or not. If you aren’t monitoring it, how can you measure it?

In addition to monitoring and baselining, industry-accepted best practices should be in place for the instance. I’ve covered memory settings, index maintenance, statistics, MAXDOP, and cost threshold for parallelism in a previous article, Common SQL Server Mishaps.

The real magic for making reports run faster is to optimize for that report. A report is essentially just another query. To tune for a slow report, that usually means you need to create indexes for that specific report or tune the code within the report. If these are reports that are hitting the OLTP database, then creating indexes for reports can impact the OLTP system by using more space, generating additional I/O for updates, inserts, and deletes, and incurring additional overhead for maintaining those indexes. You must balance the risk vs. reward. Some customers can separate the reporting database from the OLTP by using transactional replication and this allows for indexing the reporting database without impacting the OTLP database.

Although you can track report usage using the ExecutionLog, you’ll need to research the user database instance for high cost and long running queries for tuning opportunities too. DMVs and Query Store are a huge help as well, but a monitoring tool like SQL Sentry can be much more powerful and flexible. SQL Sentry doesn't have a "Reporting Services dashboard" per se, but you can create calendar views of SSRS events, easily filter built-in metrics and reports to focus on SSRS activity, and create robust Advisory Conditions to monitor the precise aspects of Reporting Services you care about (and none of the noise you don't). Even if you're not running SSRS on a SQL Server machine, you can still use Win Sentry to get rich performance insights into current and historical process- and service-level activity.

Conclusion

Tuning SQL Server Reporting Services has several unique characteristics, however standard performance tuning is still applicable for optimizing reports and monitoring the ReportServer and ReportServerTempDB databases. Backing up the encryption key is necessary for any disaster recovery or migration efforts. To better understand the usage of reports, DBAs should start using the ExcecutionLog.