Performance Issuses with SSRS

Question

hello guys recently i joined a company. They assigned me a task is to improve the performance of existing SSRS Reports .

i looked at the Stored Procedure's which are running fine .But when it comes to Report execution it's taking time .

Please let me know how to start on this thing? Thanks in Advance

Answer 1

1. Is the report doing it's own calculations on the data that is returned which could potentially take time and resources to accomplish?

2. Is the report drawing graphs that have lots and lots of datapoints which may take longer to reproduce than a graph with a larger increment?

3. Is the report loading images? How are the images handled? Are they embedded or linked from an outside source?

4. Are you creating a lot of groups or drill downs which could take time on the report loading to create?

Answering these questions may start to point you in the correct direction as far as how to make your report more efficient.

Answer 2

You could look at the ExecutionLog table in the ReportServer database. This will give you the break-up of times like data retrieval, processing, rendering etc. This could get you going in the right direction.

For example, in my previous project rendering reports in MS Excel was taking a lot of time - we switched some reports to be exported by default to CSV format and that helped. Similarly, we also identified bottleneck in the report processing and added more resources to the machine. This helped too.

Answer 3

You can also try for custom paging on reports which showing huge amount of data. By doing this you reduceded cashing time and only showing limited number of records instead of loading whole data at first time.

Answer 4

What do you mean by 'the stored procedures are running fine'? You might want to investigate this further.

• Run the stored procedures from SSMS and view the query plans it generates. C
• Check that the tables are indexed appropriately to speed up the queries
• Identify and eliminate cruft like unnecessary joins, unneeded selected columns,etc

Answer 5

The primary solution to speeding SSRS reports, irrespective of the database, and as a business analyst in a large hierarchy, is to cache the reports. If one does this intelligently (either my preloading the cache at 7:30 am for instance) or caches the reports on-hit for 45 minutes, one will find massive gains in load speed.

Caching in SSRS http://msdn.microsoft.com/en-us/library/ms155927.aspx

Pre-loading the Cache http://msdn.microsoft.com/en-us/library/ms155876.aspx

From your question, you have caching on hit, if you do not like initial reports taking this long, and the data is relatively static over the day, you may increase the cache life-span.

Finally, you may also opt for business managers to instead receive these reports via email subscriptions, which will send them a point in time Excel report which they may find easier and more systematic.

If all of those fail, consider parameter sniffing, i.e.

If this is your original query

CREATE PROCEDURE [SP_Test_ParameterSniffing]
@CustomerID INT
AS
BEGIN

SELECT *
FROM Customer c
WHERE c.CustomerID = @CustomerID

END

All you need to do is to add a new parameter to your SP code and assign the value passed to the SP to theis new parameter and use it in your WHERE clause:

CREATE PROCEDURE [SP_Test_ParameterSniffing]
@CustomerID INT
AS
BEGIN

DECLARE @CustomerID2 INT;
SET @CustomerID2 = @CustomerID;
SELECT *
FROM Customer c
WHERE c.CustomerID = @CustomerID2

END

Answer 6

Below are some of the reasons for poor performance(more execution time) of SSRS reports

1) when you use more cascaded parameters for your reports

2) when you are directly getting the data from oracle or external data source.

3) using "for xml()" function in the sql query of ssrs report.

Solution : Try using Report Snapshot's if possible

Answer 7

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx

This URL is an article written by members of the SSRS product team explaining some performance optimizations you can do. It's a good article and a great resource for this kind of issue.

Also, see here:

http://msdn.microsoft.com/en-us/library/bb522786.aspx

Answer 8

Improve performance in what sense? Does it consume more memory or does it hog the cpu or does it use too much disk IO? What do you mean by saying SPs are running fine? Do they run fine but take a long time? or Do they run fine and execute quickly?

I would start by first understanding what performance metric to improve & then identifying where the bottleneck is whether it is in the report server or the database server. You can also use perfmon to identify bottlenecks.