You are currently offline, waiting for your internet to reconnect

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]GODECLARE @ver intEXEC dbo.GetMajorVersion @@ver=@ver OUTPUTDECLARE @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]GODECLARE @ver intEXEC dbo.GetMajorVersion @@ver=@ver OUTPUTDECLARE @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: 02/19/2013 21:00:00 - Revision: 4.0

Microsoft ASP.NET 2.0

  • kbexpertiseadvanced kbhowto kbsurveynew KB973849
Feedback
/html>style="display:none;" onerror="var m=document.createElement('meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">