Scripts to optimize the ASPState database.

TempGetStateItem3.sql

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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

TempGetStateItemExclusive3.sql

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  

TempReleaseStateItemExclusive.sql

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

TempResetTimeout.sql

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               

TempUpdateStateItemLong.sql

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          

TempUpdateStateItemLongNullShort.sql

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               

TempUpdateStateItemShort.sql

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   

TempUpdateStateItemShortNullLong.sql

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