Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I run sp_whoisactive on my server it often shows create statements being run for stored procedures but this doesn't make any sense since the procedure already exists.

See the following example:

dd hh:mm:ss.mss session_id  sql_text
00 00:00:00.120 103 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
00 00:00:00.140 274 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
00 00:00:00.133 324 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]

this is from one run of sp_whoisactive it looks like there are three sessions creating the procedure. Is this something I am misinterpreting or if not how can I find out where this is coming from? These create statements are blocking actual inserts.

I am executing

EXEC sp_WhoIsActive 
@find_block_leaders = 1, 
@sort_order = '[blocked_session_count] DESC',
@get_full_inner_text = 1

the batch being executed is (which is a sproc from asp.net sql server based session state but I am seeing the exact same thing for other sprocs as well)

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

        SET @now = GETUTCDATE()
        SET @nowLocal = GETDATE()

        UPDATE dbo.ASPStateTempSessions
        SET Expires = DATEADD(n, Timeout, @now), 
            LockDate = CASE Locked
                WHEN 0 THEN @now
                ELSE LockDate
                END,
            LockDateLocal = CASE Locked
                WHEN 0 THEN @nowLocal
                ELSE LockDateLocal
                END,
            @lockAge = CASE Locked
                WHEN 0 THEN 0
                ELSE DATEDIFF(second, LockDate, @now)
                END,
            @lockCookie = LockCookie = CASE Locked
                WHEN 0 THEN LockCookie + 1
                ELSE LockCookie
                END,
            @itemShort = CASE Locked
                WHEN 0 THEN SessionItemShort
                ELSE NULL
                END,
            @textptr = CASE Locked
                WHEN 0 THEN TEXTPTR(SessionItemLong)
                ELSE NULL
                END,
            @length = CASE Locked
                WHEN 0 THEN DATALENGTH(SessionItemLong)
                ELSE NULL
                END,
            @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
                END,
            @actionFlags = CASE
                WHEN (Flags & 1) <> 0 THEN 1
                ELSE 0
                END
        WHERE SessionId = @id
        IF @length IS NOT NULL BEGIN
            READTEXT dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
        END

        RETURN 0
share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

Please show us the parameters of the execution of SP_whoIsActive. I remember there was a parameter switched between showing the actual statement or the batch/procedure that contained that statement.

If I'm not wrong:

@get_full_inner_text = 0 -- 1-shows SP/batch, 0-shows statement;

should be the one. Switch it to 0 and see if it's changing the way the output is shown.

share|improve this answer
3  
Adam Machanic describes this point Marian is making a bit more on his blog post talking about his sp_whoisactive script: sqlblog.com/blogs/adam_machanic/archive/2011/04/10/… –  Mike Walsh Nov 2 '11 at 14:50
    
Thanks reading up on it. –  olle Nov 2 '11 at 14:59
    
@MikeWalsh: thanks, great finding! I was also searching for it in the mean time :-). –  Marian Nov 2 '11 at 15:00
    
@olle switch param get_full_inner_text to 0 and you'll see which statement is the blocker. In your case I suppose it's the UPDATE statement. See what you can do to make it faster (indexes..indexes .. or maybe indexes :-) ). –  Marian Nov 2 '11 at 15:07
3  
@olle: it's not executing CREATE PROCEDURE TempGetStateItemExclusive3. It's executing only the procedure itself - TempGetStateItemExclusive3 with it's parameters. But the output of the SP_WhoIsActive show the complete text of the procedure TempGetStateItemExclusive3 for you to be able to have a bigger picture. Better check also with SQL Profiler to see all sessions. There you'll see exactly the executions. –  Marian Nov 2 '11 at 15:24
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.