How long does this report take on your SSRS 2008 R2 server?

All,

We have an issue moving from SSRS 2008 to SSRS 2008 R2.

A report on SSRS 2008 server runs in 5 or 10 seconds. When I deploy it to our 2008 R2 server, it takes around 5 MINUTES to run!

I reckon this is a bug in SSRS 2008 R2, and would like some feedback from other users.

Could some of you please deploy the attached report to your 2008 and/or 2008 R2 servers and let me know how long it takes to view the report from a browser?

I have attached the report as TestPerf.txt (you'll need to change the extension to RDL).

Thanks in advance.

TestPerf.txt (28.6 kB)

asked Apr 17 '12 at 04:25 PM in Default

xnl28
765 ● 46 ● 51 ● 58

Have you take a look on the executions statistics stored in the [ExecutionLogStorage] table in the Report Server database?

If not, take a look on the TimeDataRetrieval, TimeProcessing and TimeRendering to find out what causes the long execution.

Apr 17 '12 at 06:37 PM Pavel Pawlowski

@Pavel: Yes, I checked all those figures before posting the question. The total of TimeDataRetrieval, TimeProcessing and TimeRendering averages about 2 or 3 seconds over several runs. So the huge time in 2008 R2 is due to Internet explorer rendering the report (not the server rendering the report)

Apr 17 '12 at 06:58 PM xnl28

@xnl28 Did you seek help from Microsoft? (You said you will in earlier thread) If yes, then what is the response you got?

Apr 18 '12 at 04:37 AM Usman Butt

@xnl28 First thing noticed in your report is that Tablix's "KeepTogether" property is set to TRUE, which means that you want all your data on one page? This itself could decrease the performance. Try setting that property to FALSE and please let us know the result.

Apr 18 '12 at 10:09 AM Usman Butt

It is simple because there is a bug in 2008 R2. If you cannot live with the workarounds then wait for a CU/Service Pack OR whatever the Microsoft suggests.

Having said that, I would request not to post new questions with the same. You can always update your pending threads to seek help. This way everyone would have the background readily available and can give you better support. Thanks.

Apr 18 '12 at 11:34 AM Usman Butt

show 3 more replies

(comments are locked)

6 answers:

What makes you think it is a bug? What does the execution plan for each instance show you?

When I reviewed the plan I got from the query it shows the "Optimization Level" as "TRIVIAL". What I have read this simply means the query is not going through the full optimization process by the optimizer due to certain requirements. I don't have a SQL 2008 instance but ran the query on SQL 2008 R2 (SP1) and SQL 2005 (SP4) and got the same plan and same time to run it. I don't have a report server to deploy it to.

answered Apr 17 '12 at 06:28 PM

Shawn_Melton
4.7k ● 13 ● 18 ● 27

@Shawn: Thanks for the input.

I think it is a bug for the following reasons:

The same report (same RDL file) is executing against the same database server and viewed from the same application (IE 8.0) on the same computer; the only thing that has changed is the SSRS server to which the report is deployed, so the difference in report rendering times must be due to that.
No-one has been able to provide a way to resolve this. I have posted several questions and researched for quite some time for a resolution.
When deploying a report that works fine to a new version of SSRS causes the report to degrade in performance by a factor of 10, I would consider that a bug.

I would be more sure it was a bug if more people could try the report themselves and see what performance they get!

The poor performance is nothing to do with the query. The TimeDataRetrieval is approximately the same when viewing the report from 2008 and R2.

Apr 17 '12 at 07:17 PM xnl28

(comments are locked)