SSRS Performance Issue

Step-by-step debugging of SSRS Performance Issue:

1) If the SSRS report is associated with query or stored procedure check if it takes time.
If yes then tune the associated query or stored procedure by usual – do indexing on the where clause fields. Sometimes even including aggregation fields in select clause, too helps.

2) Introduce page breaks.

As a user pages through a report, the report processor combines data and report layout information for each report page and passes the page to the report renderer. For a report that has no page breaks, the whole report must be processed before the user can view the first page.

A soft-page break renderer, such as the HTML viewer, automatically handles paging for you. You can override this automatic behavior and set the report to be one page by setting the Report property InteractiveHeight to 0. For hard-page break renderers, you must add page breaks manually.

How to check how long the Sharepoint integrated SSRS report took?
SSRS database – Table – ExecutionLog or ExecutionLogStorage – Fields:
[TimeStart] ,[TimeEnd] ,[TimeDataRetrieval] ,[TimeProcessing] ,[TimeRendering]

TimeStart Start and stop times that indicate the duration of a report process.
TimeEnd
TimeDataRetrieval Number of milliseconds spent retrieving the data.
TimeProcessing Number of milliseconds spent processing the report.
TimeRendering Number of milliseconds spent rendering the report.
AdditionalInfo is the XML column :

Not sure TotalTimeDataRetrieval, ExecuteReaderTime is in what sec or millisec?

NOTE: If introducing pages is creating unwanted effects such as creating separate sheets in Excel then create exactly same report without page breaks and give a link to it from the report with page breaks.

3) Check if pre-populating of the parameters is causing performance issue. One of the reports we dealt with was taking 4-5 minutes. Once we removed the pre-populating of one of the parameter then it took only 4-5 seconds.

More good references:

from microsoft

from M Milligan

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s