Let’s talk about SQL Server Reporting Services and IIS.
Have you ever been in a situation where the first report of the day goes horribly slow? As a DBA your first instict might be to start troubleshooting the query that is executing the report… so you run the query yourself and things run as expected and you don’t notice any “slowness”. You then run the report yourself and again everything looks good and fast.
So what’s the problem?
One of the problems could be your IIS settings. In IIS the app pool is set (by default) to shut down after being idle for 20 minutes so if you’re the first report runner of the day… you’re the winner!!!
You get to wait the 10-20 seconds that it could take for IIS to respond to the request.
So how do we get around this?
You could adjust the Idle Timeout setting in IIS
Now depending on the separation of duties and standards in your environment playing around with IIS settings might not be something that you can control or have a say in. At the end of the day all you’re looking to do is ping your Reporting Services report server once within the Idle Timeout so that IIS doesn’t see it as being inactive and shut you down.
A quick and dirty way of doing this (that doesn’t require any changes to IIS) is to create a subscribed report. This report doesn’t have to do anything of value, all you’re looking to do here is to schedule this report to execute periodically based on your Idle Timeout settings. IIS will in turn see the Report Server as always being active and your Report Server will not be shutdown on you unexpectedly.
Enjoy!!
Reporting Services doesn’t need (or doesn’t even use) IIS in SQL 2008 (I’m not sure if I can say that for *all* circumstances though). In that case the idle timeout settings are harder to find.
But the ping solution works just as well with every version (and is probably way easier to set up.)