Microsoft SQL Server Reporting Services – measuring and improving performance

§       Optimize your report queries. Usually the bulk of report execution time is spent executing queries and retrieving results. If you are using SQL Server, tools such as Query Analyzer and Profiler can help you optimize your queries and Database Tuning Advisor can suggest better indexes for your database.

§       Measure: The Report Server ExecutionLog table contains data on reports performance. The following query can give you a quick look at how long it took to execute certain reports, and where the bulk of the time was spent. TimeDataRetrieval contains the number of ms. spent getting data from the report's data source(s).

use ReportServer

Select * from ExecutionLog with (nolock) order by TimeStart DESC

Make sure to include the “nolock” hint. The ExecutionLog table is used by the RS runtime and locking it can severely degrade your server’s performance.

A better solution is to run the DTS package that comes with Reporting Services. It will move data out of the runtime tables into a set of reporting tables. That way you are minimizing the amount of interference with the RS runtime. You can also take advantage of the built-in reports based on the execution logs, and/or build your own reports.

 §       If you don’t need data in your report, don’t retrieve it. Levering database operations such as filtering, grouping, and aggregates can reduce the amount of data that is processed in the report, improving performance.

§       Keep your reports modest in size and complexity. Do users really want to look at a 1,000 page report?

§       If performance is extremely bad even for single users, check the Application Restarts counter in the ASP.NET category; some antivirus software is known to “touch” configuration files, thus causing expansive Application Domain restarts in the report server web service.  For more information, search for articles relating to “antivirus and ASP.NET”.

§       If performance is slow on the first web service access after there have not been any accesses for a certain time period, disable the idle timeout on the Performance tab in the Application Pool in IIS Manager.

§       Execute reports from cached / snapshot data as opposed to live whenever possible.

§       Limit non-essential background processing to off-peak hours in order to avoid contention with on-line users.

§       If you load your report server up with 4GB memory, remember to set the /3GB switch in C:\boot.ini so application processes can make use of it. 

§       If a single server can’t handle the workload, consider locating the Reporting Services catalog on a remote SQL Server instance as your first step toward increasing system capacity.

§       If one report server configured with a remote catalog still doesn’t adequately support your workload, consider increasing the available resources on the system hosting your report server (scale-up) or setting up a clustered web farm of report servers (scale-out).

Comments (10)

  1. Tips for Optimising Reporting Services Performance from tudortr

  2. Nick C. says:

    Regarding your comment:

    Usually the bulk of report execution time is spent executing queries and retrieving results.

    I wish that were true in our case! We’re using Analysis Services (OLAP) with Reporting Services. While the query results are returned to the reporting server in 1 second or so, our reports take 30-60 seconds to render on a high end server! Something is very wrong on the RS side.


  3. RHIncredible says:

    I have read thru and checked every suggestion here and in many other places and can find absolutly no solution to my perfomance problem. I am running a moderatly complex query to a remote (WAN) Oracle database using the latest Oracle Data Provider and RS will not render the report, it just hangs until the process is recycled. I have tested the exact same report on a local (intranet) Oracle database and it works fine. I have tried adjusting every "time out" configuration setting both client side and server side. It won’t even generate for a subscription. The infuriating part is I can execute the query in the design tool but when attempting to preview or execute on the server I get nothing. I’m getting no Exceptions or errors or any indication why the report won’t complete processing. Any Ideas?

  4. rajkant says:

    I am facing same problem – can u please share the solution if

    you got it?

  5. Tips for Optimising Reporting Services Performance from tudortr

Skip to main content