SQL Server

Guidelines for Scaling your Reporting solution

Guidelines for Scaling your Reporting solution

Author: Basit A. Farooq

You will not always find it practical to conduct your own performance tests on a new configuration before you deploy it. When this happens, you should use monitoring to establish baseline performance measures on your existing system. You should then repeat the monitoring with the new configuration to assess the effectiveness of your scaled system.

If you identify a requirement to scale your reporting solution, you can use the following guidelines to help you to design the new system configuration.

Optimize report queries first

This is not strict guideline for scaling, but you should remember that you might waste valuable resources if you embark on scaling your reporting solution to improve report performance without first optimizing the report queries. Examples of report query include:

· Using filtering to reduce number of rows retrieved from the database. Do not retrieve data that is not used in a report.

· Processing reports from cached data or execution snapshots.

· Keeping your reports modest in size and complexity. Look for ways to break up potentially large reports into smaller chunks.

· Processing cache fills and snapshots at off-peak times when they are not in contention with on-demand report processing.

Add more memory and worker processes

Memory consumption is likely to be the first bottleneck that you can encounter in both report servers and SQL Server-based servers, and the performance of both responds well to additional memory.

If you are hosting the report server and the report catalog on the same Windows server, you should consider setting the SQL Server memory usage limit to help control competition between SQL Server and Microsoft SSRS web server for memory resources.

On a two-processor or four-processor system, you should increase the number of worker processes. On a four-processor system this has the most beneficial effect when you increase the number of worker processes from one to four, because this helps the report processes to handle larger concurrent workloads before performance degrades.

Host the report catalog on a remote server

You should move the report catalog to a remote server to create the initial configuration, which you can then scale up and scale out as necessary. This also removes competition for resources between the report server and the SQL Server-based server that is hosting the report catalog.

Create the report catalog on several files and disks

This is a SQL Server optimization technique to maximize disk I/O operations. You should create the databases for the report server catalog as several files, and if possible put the data files and log file on separate disks.

Scale out the report server

You should consider scaling out the report server in preference to scaling up. That’s because scale out causes less down time than a server upgrade; since you can prepare a new sever before you add it to your system. Scaling out also offers better availability because, if one server fails, the overall system still continues to operate. Moreover, Microsoft SSRS is specifically optimized to run on multiple servers that use inexpensive hardware in a scaled-out configuration.

Optimized snapshot storage

You should balance the resource requirements for storing, processing, and transmitting report snapshot data in your reporting solution. You should consider using snapshot compression to reduce database storage requirements if you store snapshots in the report database. You should adjust the compression configuration setting to balance saving in storage with increased processing that is required to compress and expand the data.

You should also consider using the file system storage option if you have a remote report catalog and scaled-out report servers. This eliminates both the need for database storage resources for the snapshots and the network traffic when the report server creates or retrieves a snapshot.

Consider selective processing

In addition to creating a single scaled-out report server, you might consider adding a number of independent report servers to your solution to perform some specific processing. Independent report servers share the same report catalog database, but are assigned a different Web address and port number. For example, your reporting solution can direct specific processing, such as scheduled report deliveries, to a specific independent report server, which leaves the main group of report servers free to process on-demand reports.

By dividing the processing in this way, you can isolate predictable workload from unpredictable workload and monitor the performance and resource usage of different types of workload separately. Therefore, you should also consider separating ad-hoc reporting from the rest of your reporting solution by using this kind of configuration.