人人為我 我為人人

某一天開始，突然一直收到 SSRS 發出 block 警告信

越來越頻繁而且 block duration 也有越來越久的趨勢

查看了一下 發生 blocking 都是相同的 statement

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 了

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

但 developer 也都很久都沒有異動或新增報表了呀

所以直覺這 ReportServerTempDB.dbo.SessionLock 不太尋常

於是檢查了一下發現這個 LockVersion 似乎有點古怪哩！

於是先 update statistics 再觀察

果然就再也沒有出現這個訊息了～

have fun ʕ•͡ᴥ•ʔ 

參考文件：
https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql