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.
Thank you so much for providing information about SQL and SSIS and other similar aspects needed to be looked ahead while programming.
ReplyDeleteSSIS PostgreSql Write