SSRS ReportServer Database 的Blocking问题

    我们监控SQL SERVER数据库的阻塞情况时,老是收到在SSRS 里面出现SQL阻塞情况,刚开始由于事情多,没有太关注ReportServerTempDB里面的会话阻塞情况,但是老是出现这种频繁阻塞情况,不得不仔细研究一下SSRS的Blocking问题。

技术分享

 

Blocking SQL Text

CREATE PROCEDURE [dbo].[Writelocksession] @SessionID        AS VARCHAR(32), 
                                          @Persisted        BIT, 
                                          @CheckLockVersion BIT = 0, 
                                          @LockVersion      INT 
AS 
    SET nocount OFF; 
 
    IF @Persisted = 1 
      BEGIN 
          IF @CheckLockVersion = 0 
            BEGIN 
                UPDATE [ReportServerTempDB].dbo.sessionlock WITH (rowlock) 
                SET    sessionid = sessionid 
                WHERE  sessionid = @SessionID; 
            END 
          ELSE 
            BEGIN 
                DECLARE @ActualLockVersion AS INT 
 
                UPDATE [ReportServerTempDB].dbo.sessionlock WITH (rowlock) 
                SET    sessionid = sessionid, 
                       lockversion = lockversion + 1 
                WHERE  sessionid = @SessionID 
                       AND lockversion = @LockVersion; 
 
                IF ( @@ROWCOUNT = 0 ) 
                  BEGIN 
                      SELECT @ActualLockVersion = lockversion 
                      FROM   [ReportServerTempDB].dbo.sessionlock WITH (rowlock) 
                      WHERE  sessionid = @SessionID; 
 
                      IF ( @ActualLockVersion <> @LockVersion ) 
                        RAISERROR (‘Invalid version locked‘,16,1) 
                  END 
            END 
      END 
    ELSE 
      BEGIN 
          INSERT INTO [ReportServerTempDB].dbo.sessionlock WITH (rowlock) 
                      (sessionid) 
          VALUES      (@SessionID) 
      END 

Blocked SQL Text

CREATE PROCEDURE [dbo].[Checksessionlock] @SessionID   AS VARCHAR(32), 
                                          @LockVersion INT output 
AS 
    DECLARE @Selected NVARCHAR(32) 
 
    SELECT @Selected = sessionid, 
           @LockVersion = lockversion 
    FROM   [ReportServerTempDB].dbo.sessionlock WITH (rowlock) 
    WHERE  sessionid = @SessionID 

 

如上所示,在存储过程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. Best practices for how to address this issue are documented here: http://sqlcat.com/search/searchresults.aspx?q=reporting+services&ctypes=blog We will consider this issue for a future version of Reporting Services.

 

参考资料

https://connect.microsoft.com/SQLServer/feedback/details/698388/blocking-in-ssrs-reportserver-database

http://blogs.msdn.com/b/sqlcat/archive/2013/09/20/report-server-catalog-best-practices.aspx

http://blogs.msdn.com/b/sqlcat/archive/2013/10/30/reporting-services-performance-and-optimization.aspx

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。