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.

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
    loop

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

    -Bill

  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 *