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

Saturday, January 13, 2007

Spotlight

As a technologist, I am compelled to write about toolsets that are extra-ordinary. They are few and far between as the saying goes, but once in a while a stellar performer emerges.

If you have any responsibilities in IT System Administration you probably also have end users that will typically belt-out a "why is the system running so slow today?" kind-of statement. Little do they know that there are only a gazillion (yes, that is a real number) parts and pieces between their keyboard input and monitor output.

Check out "Spotlight" from Quest Software. It is a graphical and extremely logical view of your server. Jump on their website www.quest.com and look at the details. In the meantime, here is my take on it:

The display is a left-to-right depiction of the inner workings of your computer (server or whatever). Besides the fact that it looks cool as hell, you can see instant and historic metrics of data flow from network to cpu to memory to cache to disk and back... all interactive. Cool, huh? Don't under-estimate the importance of this logical / simplistic 20,000 ft view of your system.

It gets better -- you can drill-down into anything on the screen for detail or history. Ohh my!

So, the typical end user yells "the network is slow". You look up over your shoulder and past your latte at the LCD panel on the wall and see the CPU speedo very low, but the paging meter off the scale.... This was a real scenario for me that led me down the path of "why the heck can't we use the 6gb of memory in our 32 bit server?"... but that's another post coming later.

Do yourself a favor and check this software out. You'll win the "coolness" award as well as providing a tool to your SysAdmin that will actually give them some direction into performance issues and capacity planning.

Dan Ribar



Monday, January 1, 2007

write? right!

Why would you blog about a pen? Well... my intent is to give credit, accolades or feedback whenever I'm moved... "moved" being something more then usual.

So I bought a new pen on January 1, 2006 and just replaced it 365 days later. I use one pen every single day in the office and have guarded it like my firstborn daughter. One pen - every day - day in - day out. Last Friday it finally ran out of ink.

It is the Uniball 207. Writes well, multi-copies are OK, feels good in the hand, and lasted all year.

Great product. Rated A+++

Dan Ribar