Is it necessary to create ASP.NET 4.0 SQL session state database, distinct from existing ASP.NET 2.0 ASPState DB?

There was no quick answer on this from anybody, so I did some digging. I generated an ASPState database using the aspnet_regsql.exe tool from .NET 2.0, and then I did the same thing using the same tool but from .NET 4.0. Then, I generated scripts from each of those resulting SQL Server databases and used a comparison tool to isolate the differences.

What I found is: The only material difference between the ASPState schema from .NET 2.0 to .NET 4.0 versions is the dbo.DeleteExpiredSessions stored procedure. That's the stored procedure called periodically by a SQL Server Agent scheduled job also installed by the tool.

Consequently, it would seem that the schema for ASPState 2.0 and ASPState 4.0 are perfectly compatible and so it's not necessary, from a technical standpoint, to segregate ASP.NET 2.0 and ASP.NET 4.0 session state – but I'll likely do it anyway.

(This finding was a bit surprising, as ASPState changed a lot from .NET 1.1 to .NET 2.0.)

Details for each version's changed stored proc:

.NET 2.0 ASPState DeleteExpiredSessions stored procedure:

CREATE PROCEDURE dbo.DeleteExpiredSessions
AS
    DECLARE @now datetime
    SET @now = GETUTCDATE()

    DELETE [ASPState].dbo.ASPStateTempSessions
    WHERE Expires < @now

    RETURN 0   
GO

.NET 4.0 ASPState DeleteExpiredSessions stored procedure:

CREATE 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 AND Expires < @now
                FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
            END
        CLOSE ExpiredSessionCursor
        DEALLOCATE ExpiredSessionCursor
    END 
    DROP TABLE #tblExpiredSessions
RETURN 0     
GO

As for why the above change was necessary, I found the following MSDN blog post:

Deadlock when storing Asp.net sessions in SQL server during peak load

Excerpt, in reference to the older procedure:

...
This would take the locks on all the expired records for deletion and these locks may be promoted to page locks. This can give rise to deadlocks with other ‘session state write statements’ when the number of records marked for deletion increases. By default this stored procedure is supposed to run every minute. ...

Consequently, the newer version of the stored proc may be advisable for ASP.NET 2.0 apps, too.

One more thing I learned from the blog post that I did not know: ASP.NET 4.0 session state mechanism now offers compression. Search on compressionEnabled at sessionState Element (ASP.NET Settings Schema).

Finally, I also just found something relevant from Microsoft, at ASP.NET Side-by-Side Execution Overview. Excerpt:

...
If SQL Server is used to manage session state, all versions of ASP.NET (of the .NET Framework) that are installed on the same computer can share the SQL state server that is installed with the latest version of ASP.NET. The schema for session state is the same in all versions of ASP.NET.

(Though there are some differences in implementation not specific to the schema.)

.NET 2.0 ASPState DeleteExpiredSessions stored procedure:

.NET 4.0 ASPState DeleteExpiredSessions stored procedure:

Recommended topics

Hot tags