SSRS Timeouts and Performance

By steve, 23 November, 2016

I have been debugging an issue where users of a database are experiencing slow performance periodically, which was traced to some slow SQL queries for some reports (the 50 second read operation was locking records, stopping any updates from running until the read finished). In response, we enabled SSRS caching for the slow queries (it did not matter if the reports for these datasets are 30-60 minutes behind real time), and changed the refresh interval of the report to 15 minutes instead of 5.

This caused the report to give an error every 15 minutes when it refreshed, and we were not seeing any improvements from the cache. There are 2 different issues here

We were using now() as the end date for the report, which meant that every time the report refreshed, the end date parameter was different, so the SSRS dataset cache was not used.
The solution here was to use DateAdd("s", 1, DateAdd("d", 1, DateSerial(Year(Now()), Month(Now()), Day(Now())))) as the end date (1 second before midnight tonight) to capture all data to the end of today.
The default session timeout for SSRS is 600 seconds (10 mins), so any refreshes that happen in the /ReportServer/ pages need to be less than this timeout. We had set our report to refres every 900 seconds.
Our solution was to reduce the refresh interval, but I also found the following ways to increase the timeout:
- http://social.technet.microsoft.com/wiki/contents/articles/23508.sql-se…
```
Public Sub Main()

    Dim props() as [Property]

    props = new [Property] () { new [Property](), new [Property]() }

    props(0).Name = “SessionTimeout“

    props(0).Value = timeout


    props(1).Name = “SessionAccessTimeout“

    props(1).Value = timeout


    rs.SetSystemProperties(props)

End Sub
```
  You can run this script with the following command:
  rs -i sessionTimeout.rss -s http://localhost/reportserver -v timeout=“6000“
- https://blogs.msdn.microsoft.com/jgalla/2006/10/11/session-timeout-duri…
  The default value of "SessionTimeout" is in seconds and default value is 600 and 1800 for "SystemReportTimeout".
  
  You can edit this value from ConfigurationInfo table of report server.
```
select * from ConfigurationInfo where Name in ('SessionTimeout','SystemReportTimeout')
```

Tags

Comments