We were retiring some old hardware so we decided to take the opportunity to scaleout our reporting Services.
Our original set up was two load balanced web front ends holding the web based application. The app used reporting services to render it's reports and reporting services was local on the web front ends and the reporting catalogs were on another server that held the application DB. At the time the servers were 2005 SP4 CU3.
Our desired set up is still two load balanced web front ends with the web based app. The reporting services will be on a dedicated reporting server. The catalogs and the reporting instance will be on the same server. The Apps DB will be on its own server as well.
The problem is that when we moved to the new setup performance for a few reports tanked. Seriously tanked. It was so bad that we went back to the original design, albiet with new OS and SQL Versions. Everything is Now 2008 R2 Sp2 CU5. We want to go to out scaled out design but we can't get the reports to run in a reasonable amount of time. It's not all the reports, just a few important ones.
The Stored procedure the report uses runs in about 20 seconds. When the report server is on the web front end and the catalog on the same server as the app DB the report runs fine. 10s data retrieval time and 4 Seconds render time(from ExecutionLog3).
When the Report is on the dedicated report server the Data retrieval time is 9 1/2 minutes, and the render time is 3s.
I've checked the wait stats and the App DB top wait is ASYNC_Network_IO, and the top wait for the report server is LCK_M_S. Looking close at the locks on the report server it seems there are thousands of locks on a few tables in the reportserverTEMPDB DB
Segment(4359 Locks. All X or IX)
ChunkSegmentMapping(2050 Locks. All X or IX)
SegmentedChunk (8 locks. 3 are Page IX)
I'm at a loss as what to look at. There's not a lot of data generated by the report SP, the byte count and row count from ExecutionLog3 are 30391 and 2128 respectively and our network is all Gigabit between the servers. The SP does have a lot of temp tables (23!) but it's 3rd party and not something I can change. Does anyone have any ideas at what to look at? I've found a few things like "KeepTogether" causing the rending to slowdown as it paginates everything, but I've set them all to false and the report still took 9+ minutes.
We have a good relationship with the vendor, and if I can find the problem I'm sure they'll change the report for us. Right now they think it's our network, I think it's the report format. Any Ideas?