Improving ASP.NET Session State database performance by reducing blocking

On a recent consulting engagement, I was working with a client that had significant performance issues with their ASP.NET session state database. They had a combination of both large session state and a large number of concurrent sessions. They were regularly experiencing command timeouts on that database.

In my investigation, curiously I found that a DELETE statement was the culprit. I tracked it to the DeleteExpiredSessions stored procedure. Looking at it, it seems tame enough:

CREATE PROCEDURE DeleteExpiredSessions

AS

  DECLARE @now DATETIME

  SET @now = GETUTCDATE()

  DELETE ASPState..ASPStateTempSessions

  WHERE Expires < @now

  RETURN 0

GO

However, the problem is that as session size grows, each delete takes longer and as the number of sessions grows, this simple DELETE ends up causing substantial blocking. It was at the head of nearly every blocking chain. This proc is run every five minutes.

There is no need for this proc to do all the deletes in a single operation. I replaced it with one that does a series of individual deletes:

CREATE PROCEDURE dbo.DeleteExpiredSessions

AS

  DECLARE @now datetime

  SET @now = GETUTCDATE()

  CREATE TABLE #ExpiredSessions

  ( SessionID nvarchar(88) NOT NULL    PRIMARY KEY  )

  INSERT #ExpiredSessions (SessionID)

  SELECT SessionID

  FROM [ASPState_2_0].dbo.ASPStateTempSessions

  WHERE Expires < @now

  DECLARE SessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY

  FOR SELECT SessionID FROM #ExpiredSessions ORDER BY CHECKSUM(NEWID())

  DECLARE @SessionID nvarchar(88)

  OPEN SessionCursor

  FETCH NEXT FROM SessionCursor INTO @SessionID

  WHILE @@FETCH_STATUS = 0 BEGIN

    DELETE FROM [ASPState_2_0].dbo.ASPStateTempSessions

      WHERE SessionID = @SessionID

    FETCH NEXT FROM SessionCursor INTO @SessionID

  END

  CLOSE SessionCursor

  DEALLOCATE SessionCursor

  DROP TABLE #ExpiredSessions

  RETURN 0

GO