During peak load the web server gets freeze when storing asp.net session in SQL Server?
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.ASPStateTempSe