Performance Recommendations with ASPStateDB

1. Never use the same ASPState database for two different web applications or sites.
2. The stored procedure tempresettimeout is called on every request and this can be madness on a high volume/many pageviews per visit site. 
3. There is a deleteexpiredtokens stored procedure that kills the performance under load. 

Found that a DELETE statement was the main culprit in DeleteExpiredSessions stored procedure. Looking at it, it seems applying delete operation on same table

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  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