Configurable value for the number of report executions to keep in the history (for back purpose) - by samsonfr

Status : 

 


18
0
Sign in
to vote
ID 884197 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 5/30/2014 8:42:55 AM
Access Restriction Public

Description

There is a very significant performance degradation issue with Go to report actions in Reporting Services. See this connect link for a detailed explanation and sample report:

https://connect.microsoft.com/SQLServer/feedback/details/833306/easy-to-reproduce-significant-ssrs-performance-degradation-when-using-go-to-report-actions 

Basically, after a few Go to report actions, the price paid to keep all previous executions alive is getting higher than the time it takes to render the current report.

Each time a user click on a Go to report action, a new execution id is generated, added to the View State and  a row is inserted in SessionData (TempDB database). This row is set to expire after a configured amount of time (default = 10 minutes). When the session has expired, the row will be purged.

There are two mechanism to prevent all previous executions to expire:

•	Every time the user use a Go to report action, all previous report executions get “touched” in order to prevent them to expire. It means that multiple rows in SessionData table will have their Expiration column updated. This add significant amount of time to the report rendering (it is not done on a background thread …)

•	There is a keep alive HTTP request that is done automatically if the user leave his browser open on a SSRS report without interacting with it. This too will “touch” all previous report executions to prevent their expiration.
All this was done to enable the user to use the SSRS toolbar back button. When using back, there is an event on the server with the execution id. The server is able to retrieve all information about the previous execution (ex. report, parameter values) from the SessionData table and render the report in an efficient way.

In our experience, users will never go back more than 2-3 times using back. There is currently no way to limit the history length (the number of times a user can use back) to better balance the user flexibility and the performance.

So the history keeps growing forever (particularly if Go to report is used as navigation between reports). As it grows, performance is degrading so much that users are better off closing the browser window and starting again.

There two potential solutions:
1.	Add an option to control the history length. Zero would mean its disabled completely. A value of 3 would give best performance while covering 80% of use case (for back). Default value could be unlimited to prevent a breaking change.
2.	Microsoft could keep the sessions alive from a low priority, background server thread instead of having each client wait while the server do it while generating the report. That wouldn’t be a design change, just an implementation change. The report would be rendered immediately, previous executions would be “touched” at some point. 
Sign in to post a comment.