Improving ASP.NET Session State database performance by reducing blocking
On a recent consulting engagement, I was working with a client that had significant performance issues with their ASP.NET session state database. They had a combination of both large session state and a large number of concurrent sessions. They were regularly experiencing command timeouts on that database. In my investigation, curiously I found that a DELETE statement was the culprit. I tracked it to the DeleteExpiredSessions stored procedure. Looking at it, it seems tame enough: CREATE PROCEDURE DeleteExpiredSessions AS DECLARE @now DATETIME SET @now = GETUTCDATE () DELETE ASPState .. ASPStateTempSessions WHERE Expires < @now RETURN 0 GO However, the problem is that as session size grows, each delete takes longer and as the number of sessions grows, this simple DELETE ends up causing substantial blocking. It was at the he...