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.