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
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.
Is the report doing it's own calculations on the data that is returned which could potentially take time and resources to accomplish?
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?
Is the report loading images? How are the images handled? Are they embedded or linked from an outside source?
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.
What do you mean by 'the stored procedures are running fine'? You might want to investigate this further.
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.