locked
delete ASPStateTempSessions and blocking RRS feed

  • Question

  • 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?

    Thursday, November 3, 2016 8:45 PM

All replies

  • User283571144 posted

    Hi Preguntoncojonero,

    Any suggestions and experience about it?

    According to your description, I couldn't understand clearly about your issue faced now.

    Could you please explain more about the problem?

    Do you mean you find if you delete ASPStateTempSessions the sql blocking?

    As far as I know, this is a common issue.

    Asp.net 4.0 has improved the DeleteExpiredSessions stored procedure increases performance and reduces blocking.

    More details, you could refer to follow link:

    https://support.microsoft.com/en-us/kb/973849

    Besides, I suggest you could use insert code button when you want to show some codes in the thread.

    Best Regards,

    Brando

    Friday, November 4, 2016 5:58 AM