SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Infrastructure change causes some reports to take 10X as long to generate.


Infrastructure change causes some reports to take 10X as long to generate.

Author
Message
Steven.Howes
Steven.Howes
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 2354
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?
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74707 Visits: 40985
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. [b]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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Steven.Howes
Steven.Howes
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 2354
Is this for the report server catalogs or the App DB?
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74707 Visits: 40985
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Steven.Howes
Steven.Howes
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 2354
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.
Steven.Howes
Steven.Howes
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 2354
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.
Steven.Howes
Steven.Howes
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 2354
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search