• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 51
  • Last Modified:

ASPSession State Blocking and Timeout inSQL Server

The procedure causes blocking and eventually other sessions start getting timed out in SQL Server.  Can you suggest a approach to handle this?

        CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
            @id         tSessionId,
            @itemShort  tSessionItemShort OUTPUT,
            @locked     bit OUTPUT,
            @lockAge    int OUTPUT,
            @lockCookie int OUTPUT,
            @actionFlags int OUTPUT
            DECLARE @textptr AS tTextPtr
            DECLARE @length AS int
            DECLARE @now AS datetime
            DECLARE @nowLocal AS datetime

            SET @now = GETUTCDATE()
            SET @nowLocal = GETDATE()
            UPDATE [ASPState].dbo.ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, @now),
                LockDate = CASE Locked
                    WHEN 0 THEN @now
                    ELSE LockDate
                LockDateLocal = CASE Locked
                    WHEN 0 THEN @nowLocal
                    ELSE LockDateLocal
                @lockAge = CASE Locked
                    WHEN 0 THEN 0
                    ELSE DATEDIFF(second, LockDate, @now)
                @lockCookie = LockCookie = CASE Locked
                    WHEN 0 THEN LockCookie + 1
                    ELSE LockCookie
                @itemShort = CASE Locked
                    WHEN 0 THEN SessionItemShort
                    ELSE NULL
                @textptr = CASE Locked
                    WHEN 0 THEN TEXTPTR(SessionItemLong)
                    ELSE NULL
                @length = CASE Locked
                    WHEN 0 THEN DATALENGTH(SessionItemLong)
                    ELSE NULL
                @locked = Locked,
                Locked = 1,

                /* If the Uninitialized flag (0x1) if it is set,
                   remove it and return InitializeItem (0x1) in actionFlags */
                Flags = CASE
                    WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
                    ELSE Flags
                @actionFlags = CASE
                    WHEN (Flags & 1) <> 0 THEN 1
                    ELSE 0
            WHERE SessionId = @id
            IF @length IS NOT NULL BEGIN
                READTEXT [ASPState].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length

            RETURN 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
faith victory
faith victory
1 Solution
lcohanDatabase AnalystCommented:
Did you checked the execution plan on the stored procedure?
Do you know roughly how many rows you need to update on one call? I mean I see the "WHERE SessionId = @id" and maybe is just 1 row however without having access to that table definition/data is hard to say.
Is there a CLUSTERED index on the SessionId?
If is only 1 row...it maybe that the update itself of the clustered row text data is blocking the  READTEXT function
therefore the 2 separate statements in the SP are waiting on each other in particular if parallelism is involved and you should see that in the execution plan as but as mentioned on the top.
Alternatively you should use different SQL function to return the data which was just updated by the previous statement in the same code object like SUBSTRING or TEXTPTR
faith victoryAuthor Commented:
Thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now