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

While I'm not a fan of using cursors, the use of one here is ideal. The other less than obvious advantage of this version of the proc is that the cursor is designed to traverse the table of expired sessions in random order (CHECKSUM(NEWID())). This means that if the proc isn't working fast enough, you can simply add another job to run another copy of the proc at the same time. If one copy happens to try to delete a row that's already deleted by the other copy, that's not an issue anyway.

The blocking was completely solved by replacing the proc with this version.