User-857313401 posted
Hi misters,
about DeleteExpiredSessions SP in ASPState dababase for delete ASPStateTempSessions.
aspnet 2.0
<div>CREATEPROCEDURE[dbo].[DeleteExpiredSessions]</div>
<div> AS</div> <div> DECLARE@now datetime</div>
<div> SET@now =GETUTCDATE()</div>
<div> </div> <div> DELETE dbo.ASPStateTempSessions</div>
<div> WHEREExpires <@now</div>
<div> </div> <div>
RETURN
aspnet 4.0
<div>IF (@ver >=8)</div>
<div> SET@cmd =N'</div>
<div> CREATE PROCEDURE dbo.DeleteExpiredSessions</div> <div>
AS</div> <div> SET NOCOUNT ON</div> <div>
SET DEADLOCK_PRIORITY LOW</div> <div> </div> <div>
DECLARE @now datetime</div> <div> SET @now = GETUTCDATE()</div> <div> </div>
<div> CREATE TABLE #tblExpiredSessions</div> <div> (</div>
<div> SessionId nvarchar(88) NOT NULL PRIMARY KEY</div> <div>
)</div> <div> </div> <div> INSERT
#tblExpiredSessions (SessionId)</div> <div> SELECT SessionId</div>
<div> FROM [DatabaseNamePlaceHolder].dbo.ASPStateTempSessions WITH
(READUNCOMMITTED)</div> <div> WHERE Expires < @now</div>
<div> </div> <div> IF @@ROWCOUNT <> 0</div>
<div> BEGIN</div> <div> DECLARE
ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY</div> <div> FOR SELECT SessionId
FROM #tblExpiredSessions</div> <div> </div> <div>
DECLARE @SessionId nvarchar(88)</div> <div> </div> <div>
OPEN ExpiredSessionCursor</div> <div> </div> <div>
FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId</div> <div> </div> <div>
WHILE @@FETCH_STATUS = 0</div> <div> BEGIN</div>
<div> DELETE FROM [DatabaseNamePlaceHolder].dbo.ASPStateTempSessions WHERE
SessionId = @SessionId AND Expires < @now</div> <div>
FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId</div> <div>
END</div> <div> </div> <div>
CLOSE ExpiredSessionCursor</div> <div> </div> <div>
DEALLOCATE ExpiredSessionCursor</div> <div> </div> <div>
END</div> <div> </div> <div> DROP
TABLE #tblExpiredSessions</div> <div>
RETURN 0'
Maybe problems using dbo.DeleteExpiredSessionsand procedure dbo.TempResetTimeout?
</div>
</div> <div>“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 head of nearly every blocking chain. This proc is run every five minutes. There is no need for this proc to do all the deletes in a single operation. I replaced it with one that does a series of individual deletes”</div>
<div>
“Every time a page is loaded (and, if the web app hasn't been created correctly, possibly multiple times per page load), the stored procedure dbo.TempResetTimeout is called, ensuring that the timeout for that particular
session is extended as long as they continue to generate activity. On a busy web site, this can cause a very high volume of update activity against the table dbo.ASPStateTempSessions”</div>
this page 2013/01/t-sql-queries/optimize-aspstate in sql performance
/greglow/2007/02/04/improving-asp-net-session-state-database-performance-by-reducing-blocking/#comment-1199 in blogs msmvps com
Any suggestions and experience about it?
