Saturday, December 2, 2017

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.

1 comment:

  1. Thank you so much for providing information about SQL and SSIS and other similar aspects needed to be looked ahead while programming.

    SSIS PostgreSql Write