SSRS – Timeout issue with frequently running report & Report Snapshot

When we pull the data from bulky DB/DW, query/report timeout is one of the main issue that user faces in SSRS.

We have checked on various options to resolve the SSRS timeout issue with bully DB/DW and came to conclusion that Report Snapshot is one of the best option to resolve the issues. 

Following are the reasons for choosing Report Snapshot for resolving time-out issue:

1. Sometimes, SSRS Reports are retrieving the data from database which is bulky in volume and it is transactional in nature. So there will always be chance of Locking/Deadlock with in the resources

2. The SQL view/stored procedure that we use for report data is sometimes refer many intermediate views/ tables around 10-15 views/ tables. Executing such a complex view in every 30 secs or 1 min for multiple reports will cause performance degradation and not an optimize way of utilizing the IO/memory resources. 

3. We have tested the reports with timeout issue. After setting up 0 as timeout issue, sometimes, Reports work for long time but sometimes, Report Execution session will expired after long waiting from database side.

4. If report is having default input parameters for the reports and it is best suit for Report Snapshot.

5. With Report Snapshot, no need to hit the database at every 30 secs for multiple which is very good alternative in place of keep testing/fixing the Transactions/locking/Deadlock issue with a database for which we are not the owner.

Solution Approach for implementing the Report Snapshot

1. We can create Report Snapshot for the report. Creation of Report Snapshot is very easy and less time consuming process.

2. We will schedule the Report Snapshot process to drop and create the snapshot in every 15 or 20 mins.

3. Whenever we run the report, report will show the data from Snapshot which will be very fast and there is no chance of timeout/Session expired issue.

Latency for Reporting Data

1. As we need to schedule the snapshot, there will be latency time of 15/20 mins whichever we decided compared to Latest data.

2. If 15/20 mins of Latency time is fine for Reporting then we can go with Report Snapshot and we can test in Dev/Quality before moving to production.