March 27, 2013 at 12:24 pm
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?
March 27, 2013 at 12:37 pm
after an upgrade from a lower version to a higher version, it's pretty much mandatory to rebuild statistics with fullscan;
the update engine uses different statistics/uses them differently, and performance is known to suffer until the stats get updated.
you'll see a lot of topics here like "new server slower than old server" here; it's a common issue.
updating the stats might take five minutes at the most, but you'll see performance increases immediately. edit] the code below took 3minutes and five seconds to run on my 2008R2 8 gig production database and 27 seconds on a 50 meg database with 1500 tables.
DECLARE @Exec VARCHAR(MAX)
SELECT @Exec = ''
SELECT @Exec = 'UPDATE STATISTICS ' + QUOTENAME(schema_name(schema_id))+ '.' + quotename(name) + ' WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec FROM sys.tables ORDER BY name DESC
PRINT LEN(@Exec)
PRINT @Exec
EXEC(@Exec)
Lowell
March 27, 2013 at 12:41 pm
Is this for the report server catalogs or the App DB?
March 27, 2013 at 12:44 pm
Steven.Howes (3/27/2013)
Is this for the report server catalogs or the App DB?
Any and all databases which used to exist in any version of SQL, and was restored/upgraded to a higher version (ie 2005 -->2008R2)
Lowell
March 27, 2013 at 1:49 pm
Interesting, of course it would be all DBs. I should have thought about it for 5 more seconds. I've updated the relevant DBs (I'll schedule the rest tonight) but it's still taking 9+ minutes.
March 28, 2013 at 1:57 pm
I've also tried with no success...
I inserted the data into a table and just did a select *. No gain.
I moved that table local on the same server as the reporting services. report runs in 6 Seconds.
I'm going to start looking at perfmon to see if I can see anything out of the ordinary.
April 5, 2013 at 10:14 am
I've tried a few more things to no avail. I found another report that has a large dataset and ran them both a few times.
Trouble report: Bytes Retrieved 30389 TimeDataretrieval 345631
Second report: Bytes Retrieved 48770622 TimeDataretrieval 158468
Our Vendor thinks it's our network but I don't. Not when a report on the same server gets 1000X the amount of data in a 3rd the amount of time.
I've also gutted the report to remove all the formatting and just dump a few small text columns to a table with zero expressions on it. as Simple as I can get.
I've checked the disk IO and it's fine. I'm about to look to see if things are paging, as I just noticed (why executionLog3 bothers with an XML column I don't know. Why not just make three or four more damn columns???) that them EstimatedmemoryUsageKB for the trouble report is 1,444,296. Another Ideas to look at would be greatly appreciated.
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply