x

SSRS 2005 vs 2008 R2 report Performance difference

Currently we are using SQL Server 2005 in Production and we also have testing server with SQL Server 2008 R2. and there are same set of reports in both the servers. Now the problem I am seeing is that some of the complex reports are running slow in 2008 R2 server. And the main difference is in Processing time (TimeProcessing column from ExecutionLog table).

One report I am running has one dataset and the query is returning around 86K rows and report has 9 matrix (in which I have Sum aggregation) and a chart.

2005 Server : TimeDataRetrieval : 6 sec , TimeProcessing : 51 sec, TimeRendering : 0 sec

2008 R2 Server: TimeDataRetrieval : 13 sec , TimeProcessing : 234 sec, TimeRendering : 0 sec

Note: Index structure is not same in both servers, that's why there is a difference in Data Retrieval. I am not worrying about that for now.

Here are the server comparison.

2005 Server: Quad-Core AMD Opteron Memory: 16 GB OS: Windows Server 2008

2008 R2 Server: Quad-Core AMD Opteron Memory: 32 GB OS: Windows Server 2008 R2

Size of the ReportServerTempDB is almost same ~ 600 MB in both of them and both servers are used for Reporting only.

I have Globals!TotalPages in report so I there is no On-Demand Processing in 2008 server.

So my question is what am I missing here? Why is the huge difference in Processing? I know I can do all the aggregation in sql side and reduce the number of rows. My only concern is the difference in processing time by two servers for the same report.

more ▼

asked Jun 02, 2010 at 03:41 PM in Default

pvm_25 gravatar image

pvm_25
47 3 3 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Got the answer from MSDN forum. Link

more ▼

answered Jun 08, 2010 at 01:27 PM

pvm_25 gravatar image

pvm_25
47 3 3 4

Interesting... So SRSR is slower in case there is a report with over 2000 pages. But anything over 2000 pages is not a report, but a dump. I know that they can be necessary, but I think that there should be a specialized tool for gigantic dumps like that.
Jun 22, 2010 at 02:48 PM Mark
(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.

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:

x1951
x599
x561
x252

asked: Jun 02, 2010 at 03:41 PM

Seen: 3140 times

Last Updated: Jun 02, 2010 at 03:41 PM