Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Improved DeleteExpiredSessions stored procedure in ASP.NET 2.0

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.

↑ Back to the top

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. 


↑ Back to the top

Keywords: kbexpertiseadvanced, kbhowto, kbsurveynew, kb

↑ Back to the top

Article Info
Article ID : 973849
Revision : 1
Created on : 1/7/2017
Published on : 2/19/2013
Exists online : False
Views : 200