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
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!!
Read the complete post at webtopics
Till then bye bye…