Improved DeleteExpiredSessions stored procedure in ASP.NET 2.0

Article translations Article translations
Article ID: 973849 - View products that this article applies to.
Expand all | Collapse all

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. 


Properties

Article ID: 973849 - Last Review: February 19, 2013 - Revision: 4.0
Applies to
  • Microsoft ASP.NET 2.0
Keywords: 
kbexpertiseadvanced kbhowto kbsurveynew KB973849

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com