Saturday, January 27, 2007

SSRS Reporting Services slow on first report

If you've done any work in SQL Server Reporting Services (SSRS) you may have noticed that even simple reports for the first person of the day will take a while to execute. There were a lot of posts looking for the solution... but here is how it got worked out (see the whole post at http://www.sqlreportingservices.net/Ask/5536.aspx):

============
The solution is pretty simple actually.

Microsoft does a great job of getting technologists excited about new technologies like the 'Web Gardens' in IIS 6.

The problem was two fold -- but all in IIS.

First, I had bumped the web garden setting up to '8' thinking that it would spread the processing out. Maybe if I was getting a gazillion hits a day, but the reality was that each worker process had to spin up. So if I started one browser and tried to run a report, it would spin #1 up. If I opened another browser, it would spin up #2 and so on.

Second was that I had the worker processes 'recycling' on idle. Which in a web garden of 8 would really cause delays. By the time I got to spinning up #7 or #8, #1 had already spun back down.... ARGHHHH.

Solution was to put worker processes back on '1', and turn off the recyling.

Now it stays up all the time, and responds almost instantly.

Good Luck.

Part2==============


In IIS manager, expand on your server, then look under "Application Pools".

By default, there should only be one called DefaultAppPool. If you want to preserve the default, just add a new one by right clicking on the AppPools folder.

So right click 'properties' on the app pool and then check these spots:

Recycling Tab: I only have the default top one checked for recycle after 1740 minutes. My site has enough activity to keep this one spun up.

Performance Tab: Recycle UNCHECK this one. It is the main culprit. Then at the bottom is the web garden settings that I have set to one. All the rest is turned off for me.

Health and Identity tabs I left at default.

This made a huge difference for me. Not sure about a report except that when I run reports that are SQL-based, the site is almost instantaneous including the generation of PDF output. Compare that with 45 seconds of spin-up time when they were shutting down on idle.


Update 10/1/2007: This has been in operation since 2/1 and fell off the radar.... no problems.

Good Luck.

Dan Ribar

No comments: