Instantly share code, notes, and snippets.

Embed
What would you like to do?
Scripts to optimize the ASPState database.
ALTER PROCEDURE [dbo].[TempGetStateItem3]
@id tSessionId,
@itemShort tSessionItemShort OUTPUT,
@locked bit OUTPUT,
@lockAge int OUTPUT,
@lockCookie int OUTPUT,
@actionFlags int OUTPUT
AS
DECLARE @textptr AS tTextPtr, @length AS int, @extendExpiration bit, @now AS datetime = GETUTCDATE();
SELECT @locked = Locked
, @lockAge = DATEDIFF(second,LockDate,@now)
, @lockCookie = LockCookie
, @actionFlags = Flags
, @itemShort = IIF(Locked = 0, SessionItemShort, NULL)
, @length = IIF(Locked = 0, DATALENGTH(SessionItemLong), NULL)
, @textptr = IIF(Locked = 0, TEXTPTR(SessionItemLong), NULL)
, @extendExpiration = IIF(DATEDIFF(n,@now,Expires) < ([Timeout] - 1), 1, 0)
FROM [ASPState].dbo.ASPStateTempSessions WITH(ROWLOCK,UPDLOCK)
WHERE SessionId = @id;
-- initialization
IF ((@actionFlags & 1) <> 0)
BEGIN
UPDATE [ASPState].dbo.ASPStateTempSessions
SET Flags = Flags & ~1
WHERE SessionId = @id;
-- signal to initialize
SET @actionFlags = 1;
END
-- bump up expiration
IF (@extendExpiration = 1)
BEGIN
UPDATE [ASPState].dbo.ASPStateTempSessions
SET Expires = DATEADD(n,[Timeout],@now)
WHERE SessionId = @id;
END
-- get long session data
IF (@length IS NOT NULL)
BEGIN
READTEXT [ASPState].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length;
END
RETURN 0
ALTER PROCEDURE [dbo].[TempGetStateItemExclusive3]
@id tSessionId,
@itemShort tSessionItemShort OUTPUT,
@locked bit OUTPUT,
@lockAge int OUTPUT,
@lockCookie int OUTPUT,
@actionFlags int OUTPUT
AS
DECLARE @textptr AS tTextPtr, @length AS int, @now AS datetime = GETUTCDATE(), @nowLocal AS datetime = GETDATE();
SELECT @locked = Locked
, @lockAge = IIF(Locked = 0, 0, DATEDIFF(second,LockDate,@now))
, @lockCookie = IIF(Locked = 0, LockCookie + 1, LockCookie)
, @actionFlags = Flags
, @itemShort = IIF(Locked = 0, SessionItemShort, NULL)
, @length = IIF(Locked = 0, DATALENGTH(SessionItemLong), NULL)
, @textptr = IIF(Locked = 0, TEXTPTR(SessionItemLong), NULL)
FROM [ASPState].dbo.ASPStateTempSessions WITH(ROWLOCK,UPDLOCK)
WHERE SessionId = @id;
-- initialization
IF ((@actionFlags & 1) <> 0)
BEGIN
UPDATE [ASPState].dbo.ASPStateTempSessions
SET Flags = Flags & ~1
WHERE SessionId = @id;
-- signal to initialize
SET @actionFlags = 1;
END
-- lock and read
IF (@locked = 0)
BEGIN
UPDATE [ASPState].dbo.ASPStateTempSessions
SET Expires = IIF(DATEDIFF(n,@now,Expires) < ([Timeout] - 1), DATEADD(n,[Timeout],@now), Expires),
Locked = 1,
LockDate = @now,
LockDateLocal = @nowLocal,
LockCookie = @lockCookie
WHERE SessionId = @id;
IF (@length IS NOT NULL)
BEGIN
READTEXT [ASPState].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length;
END
END
RETURN 0
ALTER PROCEDURE [dbo].[TempReleaseStateItemExclusive]
@id tSessionId,
@lockCookie int
AS
DECLARE @now datetime = GETUTCDATE();
UPDATE [ASPState].dbo.ASPStateTempSessions
SET Expires = IIF(DATEDIFF(n,@now,Expires) < ([Timeout] - 1), DATEADD(n,[Timeout],@now), Expires),
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie
ALTER PROCEDURE [dbo].[TempResetTimeout]
@id tSessionId
AS
DECLARE @now datetime = GETUTCDATE();
UPDATE [ASPState].dbo.ASPStateTempSessions
SET Expires = DATEADD(n,[timeout],@now)
WHERE SessionId = @id AND DATEDIFF(n, @now, Expires) < ([timeout] - 1)
RETURN 0
ALTER PROCEDURE [dbo].[TempUpdateStateItemLong]
@id tSessionId,
@itemLong tSessionItemLong,
@timeout int,
@lockCookie int
AS
DECLARE @now datetime = GETUTCDATE();
UPDATE [ASPState].dbo.ASPStateTempSessions
SET Expires = IIF(DATEDIFF(n, @now, Expires) < (@timeout - 1), DATEADD(n,@timeout,@now), Expires),
SessionItemLong = @itemLong,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
ALTER PROCEDURE [dbo].[TempUpdateStateItemLongNullShort]
@id tSessionId,
@itemLong tSessionItemLong,
@timeout int,
@lockCookie int
AS
DECLARE @now datetime = GETUTCDATE();
UPDATE [ASPState].dbo.ASPStateTempSessions
SET Expires = IIF(DATEDIFF(n, @now, Expires) < (@timeout - 1), DATEADD(n,@timeout,@now), Expires),
SessionItemLong = @itemLong,
SessionItemShort = NULL,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
ALTER PROCEDURE [dbo].[TempUpdateStateItemShort]
@id tSessionId,
@itemShort tSessionItemShort,
@timeout int,
@lockCookie int
AS
DECLARE @now datetime = GETUTCDATE();
UPDATE [ASPState].dbo.ASPStateTempSessions
SET Expires = IIF(DATEDIFF(n, @now, Expires) < (@timeout - 1), DATEADD(n,@timeout,@now), Expires),
SessionItemShort = @itemShort,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
ALTER PROCEDURE [dbo].[TempUpdateStateItemShortNullLong]
@id tSessionId,
@itemShort tSessionItemShort,
@timeout int,
@lockCookie int
AS
DECLARE @now datetime = GETUTCDATE();
UPDATE [ASPState].dbo.ASPStateTempSessions
SET Expires = IIF(DATEDIFF(n, @now, Expires) < (@timeout - 1), DATEADD(n,@timeout,@now), Expires),
SessionItemShort = @itemShort,
SessionItemLong = NULL,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment