Deadlock when storing Asp.net sessions in SQL server during peak load

<Snip>

When there is more than 1 server in web farm scenario it is an obvious choice to go for session out-of-proc either with SQL server or State server. While storing session with SQLServer if you see issues with SQL deadlock during peak loads involving DeleteExpiredSessions . It goes like this…

Looking at the current implementation of the DeleteExpiredSessions stored procedure:

CREATE PROCEDURE DeleteExpiredSessions
AS
DECLARE @now DATETIME
SET @now = GETUTCDATE()
DELETE ASPState.dbo.ASPStateTempSessions
WHERE Expires < @now
RETURN 0

GO

This would take the locks on all the expired records for deletion and these locks may be promoted to page locks. This can give rise to deadlocks with other ‘session state write statements’ when the number of records marked for deletion increases. By default this stored procedure is supposed to run every minute.

For Example: In one of the case. Looking at no. of records which are marked for deletion

Select count (*) from ASPState.dbo.ASPStateTempSessions WHERE Expires < getdate()

The number of record marked for expiry was 138,000 out of 146,000 records and the size of “ASPStateTempSessions” table was crossing ~20 GB!!

…

<Snip>

Read the complete post at webtopics

Till then bye bye…