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.
参考资料
http://blogs.msdn.com/b/sqlcat/archive/2013/09/20/report-server-catalog-best-practices.aspx
最新文章
- Android——SharedPreferences
- SQL Server中的事务日志管理(1/9)：事务日志概况
- 有趣的TWinControl.RecreateWnd，并分析在哪些场合使用
- elasticsearch常用的插件
- secedit
- 百度Android语音识别SDK语义理解与解析方法
- node.js(六) UTIL模块
- 【足迹C++primer】40、动态数组
- 由于空间，注定的结果——第五届山东省ACM编程比赛总结
- Oak Seeds 网站项目回顾
- 使用sysbench对mysql压力测试
- 基于Redis实现分布式锁
- 李飞飞确认将离职！谷歌云AI总帅换人，卡耐基·梅隆老教授接棒
- 设置 Visual Studio IIS Express 站点局域网访问
- JAVA中的ZoneId常用值备注
- 转://三分钟读懂Oracle数据库容灾架之DataGuard
- mysql主从(主备)同步一键配置,配自动检测功能
- spring IOC简单分析
- 跟我学SharePoint 2013视频培训课程——网站导航及页面元素（2）
- Hbase 学习（九） 华为二级索引（原理）