Improved DeleteExpiredSessions stored procedure in ASP.NET 2.0


INTRODUCTION


If both the number of sessions and the size of individual sessions increase in Microsoft ASP.NET 2.0, deleting expired sessions may affect overall performance of the ASP.NET Web site. When a deletion job is executing , any blocking that occurs on the server that is running Microsoft SQL Server will adversely affect the execution of ASP.NET requests that use SQL Server session mode.

A recent improvement to the DeleteExpiredSessions stored procedure increases performance and reduces blocking. This improvement minimizes the adverse effect on the execution of ASP.NET requests. This improvement will also be implemented in the next version of ASP.NET.

More Information


To use the new DeleteExpiredSessions stored procedure, follow these steps:


  1. Open the SQL Server Management Studio console.
  2. Click New Query.
  3. If you use a non-persistent SQL session state, paste the following script into the right pane.
    USE [ASPState]
    GO

    DECLARE @ver int
    EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
    DECLARE @cmd nchar(4000)
    IF (@ver >= 8)
    SET @cmd = N'
    ALTER PROCEDURE dbo.DeleteExpiredSessions
    AS
    SET NOCOUNT ON
    SET DEADLOCK_PRIORITY LOW

    DECLARE @now datetime
    SET @now = GETUTCDATE()

    CREATE TABLE #tblExpiredSessions
    (
    SessionId nvarchar(88) NOT NULL PRIMARY KEY
    )

    INSERT #tblExpiredSessions (SessionId)
    SELECT SessionId
    FROM [tempdb].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
    WHERE Expires < @now

    IF @@ROWCOUNT <> 0
    BEGIN
    DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
    FOR SELECT SessionId FROM #tblExpiredSessions

    DECLARE @SessionId nvarchar(88)

    OPEN ExpiredSessionCursor

    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId

    WHILE @@FETCH_STATUS = 0
    BEGIN
    DELETE FROM [tempdb].dbo.ASPStateTempSessions WHERE SessionId = @SessionId
    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId
    END

    CLOSE ExpiredSessionCursor

    DEALLOCATE ExpiredSessionCursor

    END

    DROP TABLE #tblExpiredSessions

    RETURN 0'
    ELSE
    SET @cmd = N'
    ALTER PROCEDURE dbo.DeleteExpiredSessions
    AS
    SET NOCOUNT ON
    SET DEADLOCK_PRIORITY LOW

    DECLARE @now datetime
    SET @now = GETDATE()

    CREATE TABLE #tblExpiredSessions
    (
    SessionId nvarchar(88) NOT NULL PRIMARY KEY
    )

    INSERT #tblExpiredSessions (SessionId)
    SELECT SessionId
    FROM [tempdb].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
    WHERE Expires < @now

    IF @@ROWCOUNT <> 0
    BEGIN
    DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
    FOR SELECT SessionId FROM #tblExpiredSessions

    DECLARE @SessionId nvarchar(88)

    OPEN ExpiredSessionCursor

    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId

    WHILE @@FETCH_STATUS = 0
    BEGIN
    DELETE FROM [tempdb].dbo.ASPStateTempSessions WHERE SessionId = @SessionId
    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId
    END

    CLOSE ExpiredSessionCursor

    DEALLOCATE ExpiredSessionCursor

    END

    DROP TABLE #tblExpiredSessions

    RETURN 0'
    EXEC (@cmd)
    GO
    If you use a persistent SQL session state, paste the following script into the right pane.
    USE [ASPState]
    GO

    DECLARE @ver int
    EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
    DECLARE @cmd nchar(4000)
    IF (@ver >= 8)
    SET @cmd = N'
    ALTER PROCEDURE dbo.DeleteExpiredSessions
    AS
    SET NOCOUNT ON
    SET DEADLOCK_PRIORITY LOW

    DECLARE @now datetime
    SET @now = GETUTCDATE()

    CREATE TABLE #tblExpiredSessions
    (
    SessionId nvarchar(88) NOT NULL PRIMARY KEY
    )

    INSERT #tblExpiredSessions (SessionId)
    SELECT SessionId
    FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
    WHERE Expires < @now

    IF @@ROWCOUNT <> 0
    BEGIN
    DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
    FOR SELECT SessionId FROM #tblExpiredSessions

    DECLARE @SessionId nvarchar(88)

    OPEN ExpiredSessionCursor

    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId

    WHILE @@FETCH_STATUS = 0
    BEGIN
    DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @SessionId
    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId
    END

    CLOSE ExpiredSessionCursor

    DEALLOCATE ExpiredSessionCursor

    END

    DROP TABLE #tblExpiredSessions

    RETURN 0'
    ELSE
    SET @cmd = N'
    ALTER PROCEDURE dbo.DeleteExpiredSessions
    AS
    SET NOCOUNT ON
    SET DEADLOCK_PRIORITY LOW

    DECLARE @now datetime
    SET @now = GETDATE()

    CREATE TABLE #tblExpiredSessions
    (
    SessionId nvarchar(88) NOT NULL PRIMARY KEY
    )

    INSERT #tblExpiredSessions (SessionId)
    SELECT SessionId
    FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
    WHERE Expires < @now

    IF @@ROWCOUNT <> 0
    BEGIN
    DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
    FOR SELECT SessionId FROM #tblExpiredSessions

    DECLARE @SessionId nvarchar(88)

    OPEN ExpiredSessionCursor

    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId

    WHILE @@FETCH_STATUS = 0
    BEGIN
    DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @SessionId
    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId
    END

    CLOSE ExpiredSessionCursor

    DEALLOCATE ExpiredSessionCursor

    END

    DROP TABLE #tblExpiredSessions

    RETURN 0'
    EXEC (@cmd)
    GO
  4. Click Execute.
To determine if the session state database is configured as persistent or non-persistent SQL session state, use the following specifications:
  • Non-persistent SQL session state
    ASPStateTempApplications and ASPStateTempSessions tables are created in the tempdb database to store the session data.
  • Persistent SQL session state
    ASPStateTempApplications and ASPStateTempSessions tables are created in the same database as the Session State database to store the session data.