SSRS 2008 performance very slow

Question

SSRS 2008 performance very slow

zaty

SSC Veteran

Points: 264
More actions
August 11, 2011 at 4:54 am

#241607

Hi,
I have installed SSRS 2008 on Server A .
The Reportserver Database and Source Database are on Server B.
The problem is Generating report is taking a very long time to complete.
There is no fragmentation on Source Database.
Any idea what may be the cause and how to fix this ?
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

What happens if you execute the query in SSMS?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

zaty SSC Veteran Points: 264 More actions · Answer 2

Hi,

Its completed very fast when run in SSMS.

Regards

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

This is an SSRS issue.

Are you having issues with other reports that are executed on the same Server?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

I would check a couple of things.

First look at the Reporting Services database Execution log (view or table) to see if you can determine where the hang up is.

Second, if you are using a stored procedure as your dataset source data parameter sniffing can really hang up report output times.

If your execution log does not tell you much, and you are using sproc post back and I will give you some work arounds to parameter sniffing in SSRS.

zaty SSC Veteran Points: 264 More actions · Answer 5

Hi,

Thank you for your reply.

All reports are very slow .

I used Select * from ExecutionLog with (nolock) order by TimeStart DESC against the database but could not find any error.

Imagine that to return 10 lines also took 1 minute.

Yes, we are using a stored procedure as dataset source data .

Any suggestions to resolve this ?

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 6

There are 3 columns in the execution log

[TimeDataRetrieval]

[TimeProcessing]

[TimeRendering]

That should tell you where the hold up is. If it is in your data retrieval then you might want to look into parameter sniffing.

The way I deal with SP and SSRS is in the SP are all the parameters. For example lets say there are 3 parameters, @StartDate DATETIME, @EndDate DATETIME, and @Department VARCHAR(15). I would declare 3 local variables @FromDate DATETIME, @ToTime DATETIME, and @Dept VARCHAR(15)

Then I set the local variables to the values of the input parameters. ie

SET @FromDate = @StartDate, etc.

Then use your local variables everywhere else inside your stored procedures.

zaty SSC Veteran Points: 264 More actions · Answer 7

Thanks Daniel.

What if TimeProcessing taking long time ?

Any suggestion ?

Thanks