Excessive Blocking in ReportServer and ReportServerTempDB

Question

We have an Enterprise edition SQL server 2005 64-bit instance running on a 64-bit windows 2003 2-node cluster.

We have a homegrown blocking alert that uses a blocking trace to identify processes that have blocking that has lasted over a long period of time (approx. 240 seconds).   Periodically throughout the day these blocks occur apparently related reporting services and references an object in ReportServerTempDB that I cannot identify by ObjectID (perhaps a transient temporary data table?). 

Our RedGate Response tool reports that the blocking is caused by CheckSessionLock being blocked by WriteLockSession.  From what I can tell this is normal behaviour in the Reportsevertempdb that happens when a long running query is refreshing a data snapshot on a report. 

I need to determine if this long lasting blocking is normal behaviour and should be excluded from our reporting as noise or is it indicative of a problem with the database server?

If I need to address the issue, how do I determine the actual cause?  ie the report that is causing the blocking.

Answer 1

The blocking is normal behavior so it is safe to add a rule to ignore it for your system health checker tool.

Hope that helps,
-Lukasz

---

This posting is provided "AS IS" with no warranties, and confers no rights.