SSRS ReportServer Database 的Blocking问题

  我们监控sql server数据库的阻塞情况时，老是收到在ssrs

里面出现sql阻塞情况，刚开始由于事情多，没有太关注reportservertempdb里面的会话阻塞情况，但是老是出现这种频繁阻塞情况，不得不

仔细研究一下ssrs的blocking问题。

blocking sql text

blocked sql text

如

上所示，在存储过程writelocksession中更新[reportservertempdb].dbo.sessionlock的时候使用行锁

with(rowlock),它阻塞了存储过程[dbo].[checksessionlock]查询表

[reportservertempdb].dbo.sessionlock，这个是因为ssrs通过sessionlock表来实现多线程机制，一旦没

有线程访问这些报表时，锁才会移除。比较慢、耗时长的查询会导致这类blocking出现。

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.

i have the same problem. msdn says

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

the only thing you can do is to reduce report data or run it at quiet

time. i am sure msdn understands why it was set this way originally. you

are editing a blackbox at your own risk.

微软给出的解释是： ssrs 数据库的架构设计，在高负荷的时候导致blocking出现，你应该注意采纳一些 reporting services performance optimization的建议。减小报表的数据量等…

thank you for filing this issue.

the rs database architecture can lead to blocking under heavly load.