0

Background:

We recently migrated to SSRS 2014; our source database is SQL Server 2008 R2. We ran into some performance problems on a report where it simply would stop responding and ultimately timeout after 30 minutes when rendering the report. Reviewing the execution log on the reporting server indicates that the data retrieval time is low, while the report rendering time is high. The solution our DBA discovered after a month of this problem, is to set READ_COMMITTED_SNAPSHOT ON.

This seems to have cleared up the very strange performance problem with a single report. This report would seemingly become deadlocked when users attempted to generate it. The deadlock appears to be on the temp report server database, occurs only when returning the report to the browser (IE or Chrome), and does not occur every time the report is generated, but may be occurring when multiple users are attempting to generate the report around the same time frame. The report has 3 levels of grouping, and returns a variable amount of rows for the entered parameters.

This setting (READ_COMMITTED_SNAPSHOT) seems to now have caused a secondary problem: in the navigation bar of the report, where the user can navigate through the pages on the report, initially displays 0 of 0 when an active "Next page" button. When the user clicks the "Next page" button, the browser performs a post-back (like it would if you were navigating to the next page) and refreshes the screen with the first page. Now, the navigation bar displays 1 of X.

All of our SSRS servers received READ_COMMITTED_SNAPSHOT ON, so I had asked the DBA to update my DEV server to OFF. When this was done, I re-generated all of the reports (about one dozen), and each of them initially displayed 1 of X pages, as I would have expected. Now, I question whether setting this property to ON was the correct approach for fixing the report.

I thought about whether creating a report snapshot would be a good solution, unfortunately, I don't think it is as we have users which have differing parameter selection.

Questions:

  1. Was there a better way we should have solved the report performance problem above rather than setting READ_COMMITTED_SNAPSHOT to on?
  2. Does READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION need to both be set to true/on if one is?
  3. Has anyone encountered this problem related to the Navigation Bar in SSRS, and how were they able to resolve the issue?

Update:

We ultimately had to roll-back the READ_COMMITTED_SNAPSHOT setting, as it caused an entirely different error to occur. The problem outlined above, also seemed to clear itself up, with no issues reported two months in a row. Unfortunately, no one knows what was causing the issue in the first place, or what may have fixed it (possibly a hardware change).

Russ
  • 678
  • 8
  • 26

1 Answers1

0

I'm assuming you changed these settings on the source database of the reports, not the SSRS server itself (unless they are the same server). Changing the settings of READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION can have wide-ranging effects on your systems, so should be done with caution. See https://dba.stackexchange.com/questions/5014/what-risks-are-there-if-we-enable-read-committed-snapshot-in-sql-server for potential issues related to changing these settings (and also an answer to your second question).

To me it sounds like the problem is in your report query or queries, so changing these database level settings to resolve a performance problem in one report is probably overkill. I would first look at analysing your report queries and tuning them (and possibly adding missing statistics or indexes to the source database) before altering row versioning settings.

As to the navigation bar issue, perhaps the changes made to the row versioning mean the SSRS is unable to calculate the number of pages available when the page is initially loaded. However I don't know why that might happen.

Community
  • 1
  • 1
Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
  • We added READ_COMMITTED_SNAPSHOT ON to the SSRS Temp Database; not the source database of the reports; as the Execution log indicated that the DataRetrieval time was low, while the ReportRendering was time high. The navigation bar issue seemed to clear up when we set READ_COMMITTED_SNAPSHOT OFF on a Dev server for testing. However, we are unable to test the performance of the reports in pre-prod environments, because the concurrent users aren't available in pre-prod. – Russ Sep 02 '15 at 04:29
  • The Reporting Services documentation explicitly states that READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION should be set to OFF for both the Report Server and Report Server Temp DB databases. Changing this to ON puts you in the position of having an unsupported configuration of Reporting Services: https://msdn.microsoft.com/en-us/library/ms159093%28v=sql.105%29.aspx – Nathan Griffiths Sep 02 '15 at 04:36
  • Nathan, thank you for the references. I will review them and forward them onto the database folks as well. What would be a possible cause for the report rendering to seem like the bottleneck in the execution log? The query, when ran (or the execute of the stored proc) in ssms returns in an acceptable time (~<10s); even when executing during the time frame in which performance problems are being reported. – Russ Sep 02 '15 at 06:19