SSRS Blocking Processes

Question

We have a busy SSRS Reporting server (SQL Server 2008 R2) on which we see frequent blocking between the below processes:

• [dbo].[CheckSessionLock]
• [dbo].[WriteLockSession]

We are aware that some reports are running unacceptably slow. What I need to know is whether the blocking in the ReportServer database is symptomatic of an overloaded server or whether the blocking is causing the reports to run slow.

I am inclined to believe it is the first case but haven't been able to gather evidence prove it definitively as such.

I have read the thread at https://connect.microsoft.com/SQLServer/feedback/details/698388/blocking-in-ssrs-reportserver-database in which the issue is loosely acknowledged but I cant get to the link cited for addressing this issue.

Has some out there got to the bottom of same problem with blocking in the ReportServer database?

Answer 1

Unfortunately there is not much you can do about this, this behaviour is by design. The problem manifests itself when user sessions time out because the report is taking too much time. You can try to improve the reports, or configure the session timeout to be a bit longer than the longest running report

See this link for an explanation about the why and some ways to work around it.

You can increase the session timeout as some sort of workaround, the script is documented in the article.

From the article:

However, there is one scenario where the ping doesn't work, and that is during the inital report execution. The problem is that while the report is being executed, the user's session is locked (as we are populating the user's temporary snapshot) and the keepalive from the viewer control will be blocked. Normally, this is not a problem because report executions aren't supposed to take a long time and quite often they finish before the session timeout hits. Unfortunately, there are some cases where reports (for whatever reason) take an incredibly long time to execute. What happens in this case is that the user's session is aged out while the report is being executed, resulting in all sorts of strange behavior.

Answer 2

Assuming you have SQL 2008R2 or above.

Yes we have also witnessed the same on one of our heavy used SSRS reporting server and while troubleshooting came across this connect item, which is registered as a bug.

As explained:

SSRS issues these locks to provide a multithreading mechanism. Once all threads running the report that you wrote, end, the lock is removed. So if you see these, normally, it is a slow query on a different server causing the problem.

Some issues may happen on heavy load (or so they say), and some report that collation issues prevent CleanExpiredSessions from running.

Also,

This behavior is by design. To keep its sessions alive, viewer control tries pinging them every 10 minutes. On the server side it results in invoking GetSessionData stored proc, which starts from calling CheckSessionLock stored proc, which tries reading session write lock. If by the time when pinging happens report processing is still running, then CheckSessionLock blocks because session write lock is held while report is being processed. GetSessionData timeout is 10 minutes (“SessionAccessTimeout” system property). If report processing takes more than 20 minutes, GetSessionData timeout happens and you see this problem.

For more read and resolution on this refer to Session Timeout during execution

Answer 3

locking/blocking is to ensure consistency, and is normal behavior.

you can try read committed snapshot isolation level for the ReportServerTempDB database and SET ISOLATION LEVEL SNAPSHOT in WriteLockSession procedure

I didn't implement the solution and would not recommend it ..

the only solution to this problem is to use another reporting tool