When scaling-out your SQL Server Reporting Service, it is essential to consider how your I/O will behave and how you can better utilize your disk configuration for optimum read and write.

One strategy is to partition the Reporting Service TempDB, which can be split onto multiple disks to better take advantage of the underlying SAN or a RAID configuration, and improve request parallelism, especially important when considering a large multi-user deployment of Reporting Service.

What we are actually doing is assigning more than one physical file to the ReportServer$TempDB‘s PRIMARY, which allows us to partition TempDB’s main FileGroup into independently stored files.

This can be accomplished by the following SQL Server query (Warning! this will actually drop the SSRS TempDB database and all custom objects within the db).

You will need to adjust the initial TempDB partition sizes as well as file growth strategy as per the expected load. It is highly recommended to monitor the new partitioned deployment by setting up counters and data collectors.

Since we dropped the ReportServer$TempDB database while rebuilding the PRIMARY FileGroup into 10 partitions, we need to setup the SSRS security role on the database again, this can be accomplished by the SQL Server statement below.