ReportServerTempDB IO Saturation

  • Article

Reporting Services uses a temporary database for storage of objects which are, well, temporary.  For example, report snapshots which are associated with a particular user session as the result of a live execution.  Cached report snapshots are also stored here because they begin life as a result of a live report execution.  This means that in systems where there are a lot of live report executions happening, we are churning through a lot of data in the ReportServerTempDB.  As your system scales out or up, you will undoubtedly experience a problem where you will begin to saturate the IO subsystem hosting the ReportServerTempDB.

Do I have an IO throughput issue?  

There are lots of really great articles written about this topic, but in general there are a couple of things which I look at:

  • Perfmon counters for Physical Disk Usage:
    • Avg. Disk Sec/Read
    • Avg. Disk Sec/Write
  • Check SQL Server Wait types
    • sys.dm_os_wait_stats
    • Look for high counts of wait stats like IO_COMPLETION, ASYNC_IO_COMPLETION, and the PAGELATCH_* class of waits

Ideally, you want to see Avg Disk Sec/Read or Write to be < 10ms. If you start seeing these counters creep over 50ms, then you are probably getting into a situation where the disk is not keeping up with the incoming rate of IO requests and this is causing waits.

So how do I deal with it?

The answer is pretty straightforward.  You start adding more disks to handle the IO load on the ReportServerTempDB.  Doing this is actually pretty easy.  From within Management Studio, find the ReportServerTempDB database in the Object Explorer.  Right click and go to properties.  From the tabs on the left side of the Database Properties dialog, you will see a "Files" section, go to that guy and start adding files.  Of course, you will want to spread the files across multiple disks if you are trying to increase IO performance.  Additionally, I would recommend sizing the files to an appropriate value rather than relying on autogrowth since autogrowth can cause performance issues when SQL Server actually has to grow the files.  The exact value will be a function of how much data is incoming.  An important fact to keep in mind though is that the ReportServerTempDB database should automatically remove "stale" data as user sessions and cached reports expire, so the data shouldn't in general continuously grow.

Once you have created the additional data files, SQL Server will automatically begin to load balance IO operations across the various files, so there shouldn't be anything else you have to do.  Nice!