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
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 < @now --EXPIRED SESSION LOGGING /* DECLARE @ExpiredSessionCount Int; SELECT @ExpiredSessionCount = COUNT(SessionID) FROM @tblExpiredSessions; */ IF @@ROWCOUNT <> 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 EXCEPTION END CATCH; */ RETURN 0
- The log file is filling up and in some cases forcing autogrowth, despite the db being in simple recovery
In addition to these, a trace reveals multiple requests for the same session coming in quick succession. For example
exec dbo.TempResetTimeout @id=N'32gghltsuoesnvlzbehchp2m2014c0f1' 14 times in <1 second, so we’re wondering about this as a cause or congestion, but not sure of the root of this behavior.
Any suggestions or explanation around this behavior would be appreciated.
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).
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.