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





  DELETE ASPState..ASPStateTempSessions

  WHERE Expires < @now




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


  DECLARE @now datetime



  CREATE TABLE #ExpiredSessions

  ( SessionID nvarchar(88) NOT NULL




  INSERT #ExpiredSessions (SessionID)

  SELECT SessionID

  FROM [ASPState_2_0].dbo.ASPStateTempSessions

  WHERE Expires < @now





  DECLARE @SessionID nvarchar(88)


  OPEN SessionCursor

  FETCH NEXT FROM SessionCursor INTO @SessionID


    DELETE FROM [ASPState_2_0].dbo.ASPStateTempSessions

      WHERE SessionID = @SessionID

    FETCH NEXT FROM SessionCursor INTO @SessionID


  CLOSE SessionCursor

  DEALLOCATE SessionCursor


  DROP TABLE #ExpiredSessions



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.

7 thoughts on “Improving ASP.NET Session State database performance by reducing blocking

  1. Greg, there is a better way that doesn’t involve cursors and it’s much easier on the system. Forgive my SQL, i am not sitting in front of query analyzer and SQL is my occasional language, not primary. The functionality is achieved by looping through the temp table by taking advantage of the fact that it has a unique key. This trick was shown to me by a very wise old crusty DBA and I resolved many perf problems with it.

    declare @MinSessionID nvarchar(88)

    select @MinSessionID = min(SessionID)
    from #ExpiredSessions

    while @MinSessionID is not null

    DELETE FROM ASPStateTempSessions
    WHERE SessionID = @MinSessionID

    select @MinSessionID = min(SessionID)
    from #ExpiredSessions
    where SessionID > @MinSessionID

  2. Frank,

    I’m not convinced that the WHILE loop is faster. Most of my testing of a read-only cursor and a WHILE loop has indicated the cursor is faster. It really depends on how efficient the SELECT statement is in the cursor.

    The biggest reason is that the cursor executes the query once but the WHILE loop executes it multiple times. I used to write WHILE loops but now I start with a read-only cursor in situations like this.

    (And whenever I make any broad statements like this I always encourage people to test it in their particular situation.)


  3. No because the DELETE itself is a transaction and fills the transaction log with the deleted lines.
    The more there are lines to delete, the more the log is filled, and the more it takes time to delete (don’t forget that it deletes from index tables also).

    An idea would be to have a while loop with your line AND a TOP xxxx query (xxxx to be defined)
    Of course don’t put a transaction around this !
    And as a stored proc is a transaction by itself, you may add further hints to disable the transaction log for the SP (it can not be disabled for atomic operation like a delete).

    Something like:

    declare @count int
    set @count = 1
    SET ROWCOUNT 100 –Same as delete top 100

    while @count != 0
    set NOCOUNT off
    DELETE ASPState..ASPStateTempSessions
    WITH(ROWLOCK,READPAST) WHERE Expires < @now set @count =@@rowcount loop B.

Leave a Reply

Your email address will not be published. Required fields are marked *