x

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][1] (you'll need to change the extension to RDL).

Thanks in advance.

[1]: http://ask.sqlservercentral.com/storage/temp/174-TestPerf.txt
TestPerf.txt (28.6 kB)
more ▼

asked Apr 17, 2012 at 04:25 PM in Default

xnl28 gravatar image

xnl28
895 56 60 62

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, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 11:34 AM Usman Butt
show all comments (comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Microsoft have confirmed the issue I've described is a bug, and it will be fixed in the first service pack of SQL 2012. Hotfixes for previous versions of SQL can be obtained from Microsoft. Please see Connect ID 737342.

Thanks to all for your suggestions and advice.
more ▼

answered Jun 19, 2012 at 04:45 PM

xnl28 gravatar image

xnl28
895 56 60 62

Did you tested this with SP1 for SQL 2012? As SP1 was released about 2 weeks after the original release of SQL Server. :-)
Jun 19, 2012 at 07:34 PM Pavel Pawlowski
I didn't know SP1 for SQL 2012 was out - I can't find any article about it on MSDN...
Jun 21, 2012 at 10:50 AM xnl28
There are 2 Cumulative Updates , but no Service Pack yet: http://support.microsoft.com/kb/2692828
Jun 21, 2012 at 12:01 PM Kev Riley ♦♦

Ah, you are right @Kev. It's cummulative update. But anyway the @@VERSION tells SP1 :-)

Microsoft SQL Server 2012 - 11.0.2316.0 (X64) Apr 6 2012 03:20:55 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)
Jun 21, 2012 at 12:23 PM Pavel Pawlowski
@Pavel the SP bit refers to the windows server build - I too have been confused by this message before!!!
Jun 21, 2012 at 12:29 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

@xnl28 - from your description it really does sound like a bug - I would not continue searching for other affected people, but rather file a bug with Microsoft. Either open a support call (may cost you money), or file a connect item over at [http://connect.microsoft.com/][1].

[1]: http://connect.microsoft.com/
more ▼

answered Apr 19, 2012 at 09:27 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

excellent! I have voted on that too.

Maybe MS will fix it - have you tried out SQL 2012 to see if it has been fixed through that release?
Apr 19, 2012 at 01:44 PM WilliamD
Great, thanks. I've not tried it in SQL 2012, that's a good idea.
Apr 20, 2012 at 07:42 AM xnl28
I have just tried the report in SSRS 2012 (version 11.0.2100.60) and the issue remains. It takes 86 seconds for the report to complete compared to 7 seconds in SSRS 2008.
Apr 27, 2012 at 07:59 AM xnl28
(comments are locked)
10|1200 characters needed characters left
Does this report consistently run slower on your 2008R2 server or just the first time you run it?
more ▼

answered Apr 17, 2012 at 04:38 PM

Tim gravatar image

Tim
36.4k 38 41 139

@Tim: It consistently runs slowly.
Apr 17, 2012 at 05:03 PM xnl28
Have you tried running IE as administrator? Right click, chose run as administrator. Are you running the report through IE on a client or on the server?
Apr 17, 2012 at 07:03 PM Tim

@Tim: I don't think running IE as administrator would make a difference, because when use IE to run the report deployed to the SSRS 2008 server, it completes in 5 - 10 seconds.

Have you tried running the report I attached in the original post? I am more interested in what performance other people get when running the report in 2008 and 2008 R2.
Apr 17, 2012 at 07:27 PM xnl28
@Tim: I tried the suggestion of running Internet explorer as administrator and running the two reports again. The results are the same: When browsing to the report in SSRS 2008, it took 7 seonds. When browsing to the report in SSRS 2012, it took 78 seonds.
Apr 27, 2012 at 08:02 AM xnl28
(comments are locked)
10|1200 characters needed characters left

So it seems to relate to the same issue like in your previous post. The KB [FIX: Performance decreases after you move a large report to SQL Server 2008 R2 Reporting Services][1].

Maybe a trying a workaround mentioned in the BK

Optimize the pagination for the report when you design the report.

Don't have R2 instance and time currently available to play with it. But if will have a little more time I will try to test it on R2 instance.

[1]: http://support.microsoft.com/kb/2506799/en-us
more ▼

answered Apr 17, 2012 at 09:18 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

@Pavel: Yes, it's the same issue. In this question, I am asking other users to post the performance times they get with the test report I attached.

The R2 server I'm using is on cumulative update #5 for SSRS 2008 R2 service pack 1, which is version 10.50.2806.0. This should include the fix you refer to, but the performance is still a serious problem, so there is another issue that requires fixing.

Thanks for your suggestions. If you do get time to test the RDL I attached, that would be very helpful.
Apr 17, 2012 at 09:46 PM xnl28
(comments are locked)
10|1200 characters needed characters left

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][1] 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.

[1]: http://sqlinthewild.co.za/index.php/2009/12/08/are-trivial-plans-cached/
more ▼

answered Apr 17, 2012 at 06:28 PM

Shawn_Melton gravatar image

Shawn_Melton
5.4k 20 21 29

@Shawn: Thanks for the input.

I think it is a bug for the following reasons:

  1. 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.
  2. 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.
  3. 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, 2012 at 07:17 PM xnl28
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x558
x84

asked: Apr 17, 2012 at 04:25 PM

Seen: 2354 times

Last Updated: Jun 21, 2012 at 12:36 PM