ASPState database locking and growth problems

We use an ASPState database to persist .NET Session state on a SQL Server 2005 cluster. We are seeing some strange behavior during peak periods

-
The DeleteExpiredSessions proc is run every minute via an agent job. Sometimes this job is taking many minutes to run and delete expired sessions

-
Requests from the application to the ASPState database are very slow. I believe this is because there are exclusive locks being held on the table by DeleteExpiredSessions procedure

Code:

```
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteExpiredSessions]
AS
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

DECLARE @now datetime
SET @now = GETUTCDATE()

DECLARE @tblExpiredSessions TABLE
(
SessionID nvarchar(88) NOT NULL PRIMARY KEY
)

INSERT INTO @tblExpiredSessions (SessionID)
SELECT SessionID
FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
WHERE Expires 0
BEGIN
DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR SELECT SessionID FROM @tblExpiredSessions ORDER BY CHECKSUM(NEWID())

DECLARE @SessionID nvarchar(88)

OPEN ExpiredSessionCursor

FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID

WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
END

CLOSE ExpiredSessionCursor

DEALLOCATE ExpiredSessionCursor

END

--EXPIRED SESSION LOGGING
/*
BEGIN TRY
INSERT INTO DeleteExpiredSessionLog(RunStart, RunEnd, ExpiredSessionsDeleted)
VALUES (@now, GETUTCDATE(), @ExpiredSessionCount);
END TRY
BEGIN CATCH
--SWALLOW ANY E

I suspect you implemented Greg's replacement procedure as pre-emptive optimization. The one-row-at-a-time approach restricts locking to a single row, sure, but it's going to take a lot longer - especially if you force SQL Server to attack rows in a random order.

My suggestion would be to revert to the original procedure:

ALTER PROCEDURE dbo.DeleteExpiredSessions
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @now DATETIME;
  SET @now = GETUTCDATE();

  DELETE ASPState..ASPStateTempSessions 
    WHERE Expires < @now;
END
GO

If you find that this becomes a performance issue because of the locks taken, you can split this out and even reduce impact on the logs using something like:

ALTER PROCEDURE dbo.DeleteExpiredSessions
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @now DATETIME, @c INT;
  SELECT @now = GETUTCDATE(), @c = 1;

  BEGIN TRANSACTION;

  WHILE @c <> 0
  BEGIN
    ;WITH x AS 
    (
      SELECT TOP (1000) SessionId
        FROM dbo.ASPStateTempSessions
        WHERE Expires < @now
        ORDER BY SessionId
    ) 
    DELETE x;

    SET @c = @@ROWCOUNT;

    IF @@TRANCOUNT = 1
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
  END

  IF @@TRANCOUNT = 1
  BEGIN
    COMMIT TRANSACTION;
  END
END
GO

This is going to be much better, IMHO, than using a randomized cursor to delete one row at a time. You can tweak the value in TOP (1000) based on actual observation.

A couple of other ideas that might help:

(a) set the ASPState database's recovery model to simple (it defaults to full).

(b) change ASPState_Job_DeleteExpiredSessions to run every 5 or 10 minutes instead of every minute. If this job is taking > 1 minute to run then it is going to always be running. Hopefully it isn't completely linear, meaning that waiting 5 minutes instead of 1 won't queue up more than 5 minutes of work instead of 1. Implementing my suggestions above should help with this.

StackExchange Database Administrators Q#30445, answer score: 5