SQL Server Reporting Services(SSRS) - Performance Monitoring & Optimization

Common Report Performance Issues

• Data takes too long to retrieve
• Report takes too long to process
• Report takes too long to render
• Report Time out Issues
• Unresponsive State
• Interactive Sort, Drill down too slow
• Report Failures

Ways to monitoring Report Performance

• Task Manager
• Event Viewer
• Native Report Server views and Query
• DMVs
• Performance Monitor
• Third Party Tools

SSRS Performance Optimization Tips

• Use 64 bit hardware and software
• Use Report Execution Timeouts to control how a long a report has to execute
• Control the size of the report using input parameters
• Subscriptions or Interactive Report?
• Live Data or Snapshots?
• Disable report history to reduce/remove the report history snapshots
• Set proper Memory Limit Configuration

–        Default 60% of available memory. Max can be 80 %

–        Once threshold hit, no new requests are accepted

§  Report Snapshot

§  Report Caching

• Monitoring Performance by using ExecutionLog2 view
• Optimize disk I/O subsystem for maximum performance
• Optimize network links to ensure sufficient bandwidth

Design tips for Optimizing Report Processing

• Set CanGrow and CanShrink on text boxes to FALSE.
• Set AutoSize on images to a different value such as Fit.
• For text boxes, avoid setting the property TextAlign to General
• Avoid horizontal page breaks when they are not required
• Set the KeepTogether property on Tablix members to FALSE
• Filter, sort, and aggregation is more efficient on the data source than during report processing
• Sort the data at database query level
• Perform calculations at database level
• Consider the amount of data needed for a chart or gauge
• Drill through is better choice as compare to on demand processing
• Expressions in the Page Header or Footer Force All Pages To Be Processed
• Add page breaks where it makes sense
• Verify that InteractiveHeight is not 0
• Do use sub reports when there are just a few subreport instances.
• Do not use sub reports inside a group when there are many group instances
• In order to run large reports, there are two time-outs you must adjust: report execution time-out and the ASP.NET time-out.
• Merged cells and unaligned report items interfere with Excel functionality in the exported report.