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
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.
DBMS 数据库管理系统 DataBase Management System 将编程看作是一门艺术,而不单单是个技术。 敲打的英文字符是我的黑白琴键, 思维图纸画出的是我编写的五线谱。 当美妙的华章响起,现实通往二进制的大门即将被打开。
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
相关文章
- DBA 面对的几种 数据库“问题” 制造者 (Database killer)
- ORA-01183: cannot mount database in SHARED mode ORACLE 报错 故障修复 远程处理
- ORA-19736: can not plug a tablespace into a database using a different national character set ORACLE 报错 故障修复 远程处理
- ORA-27364: remote database string already registered as string ORACLE 报错 故障修复 远程处理
- ORA-29343: user string (mapped from user string) does not exist in the database ORACLE 报错 故障修复 远程处理
- ORA-01120: cannot remove online database file string ORACLE 报错 故障修复 远程处理
- ORA-01586: database must be mounted EXCLUSIVE and not open for this operation ORACLE 报错 故障修复 远程处理
- ORA-01670: new datafile string needed for standby database recovery ORACLE 报错 故障修复 远程处理
- ORA-12315: database link type is invalid for the ALTER DATABASE statement ORACLE 报错 故障修复 远程处理
- ORA-16206: database already configured as Logical Standby database ORACLE 报错 故障修复 远程处理
- ORA-16436: ALTER DATABASE … RECOVER FINISH could not start ORACLE 报错 故障修复 远程处理
- ORA-16622: two or more broker database objects resolve to the same database ORACLE 报错 故障修复 远程处理
- ORA-16784: database name specified by Dependency property is incorrect ORACLE 报错 故障修复 远程处理
- databaseCRS: Unlock the Potential of Oracle Database(crsoracle)
- gOracle Database 10g的优势(oracledul10)
- Oracle Raman:Uncovering the Power of Database(oracleraman)
- database打造世界级的MySQL数据库(worldmysql)
- Optimizing MySQL Performance: Tips and Techniques for Faster Database Operations(optmysql)