Post reply

Multiple Entries For the Same SPID in sysprocesses Table

  • June 23, 2009 at 7:39 am

    #132180

    Hi All,

    I've written a below cursor which will give me list of select query to find the count of records for each of my user table.

    Cursor Body

    ===

    DECLARE @QRY VARCHAR(1000)

    DECLARE @TBL_NAME VARCHAR(100)

    DECLARE TEMP_DATA1 CURSOR FOR

    SELECT NAME FROM dbo.sysobjects WHERE TYPE = 'U'

    ORDER BY NAME

    OPEN TEMP_DATA1

    FETCH NEXT FROM TEMP_DATA1

    INTO @TBL_NAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @QRY = 'SELECT COUNT(*) AS TOTAL_RECORDS, ''' + @TBL_NAME + ''' AS TABLE_NAME FROM [' + @TBL_NAME + ']'

    PRINT @QRY

    FETCH NEXT FROM TEMP_DATA1

    INTO @TBL_NAME

    END

    CLOSE TEMP_DATA1

    DEALLOCATE TEMP_DATA1

    ===

    And it will print one select query for each of your user table and output will be sumthing like below.

    SELECT COUNT(*) AS TOTAL_RECORDS, 'abcd' AS TABLE_NAME FROM [abcd]

    Now I copied all the select queries from the output and opened a new session and ran all in a single go. and in parallel to it I opened one more query window where I ran below query

    SELECT * FROM MASTER.dbo.SYSPROCESSES

    WHERE SPID = my_spid (the sp id of the window I'm running all select queries)

    And in the output of this select query I'm seeing 5 entries for the same spid in sysprocesses table and below id output.

    ==========

    60 3876 0 0x0208 7250 CXPACKET 22 1 55813 86159 0 2009-06-23 14:14:43.243 2009-06-23 14:23:08.590 0 0 sleeping 0x010500000000000515000000C9D91AA528AE7CF33130568B4A06000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MYUKVWHLUACTX01 SQL Query Analyzer 4592 SELECT HL01 DBAdmin 005056AF21CE TCP/IP HL01\DBAdmin 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01001600A2ABE328C0C091760000000000000000 12360 12524

    60 752 60 0x0404 0 LATCH_EX PARALLEL_PAGE_SUPPLIER (31AA0DD4) 22 1 672 86159 0 2009-06-23 14:14:43.243 2009-06-23 14:23:08.590 4 0 sleeping 0x010500000000000515000000C9D91AA528AE7CF33130568B4A06000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MYUKVWHLUACTX01 SQL Query Analyzer 4592 SELECT HL01 DBAdmin 005056AF21CE TCP/IP HL01\DBAdmin 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0

    60 4912 0 0x0000 0 LATCH_EX PARALLEL_PAGE_SUPPLIER (31AA0DD4) 22 1 468 86159 0 2009-06-23 14:14:43.243 2009-06-23 14:23:08.590 3 0 sleeping 0x010500000000000515000000C9D91AA528AE7CF33130568B4A06000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MYUKVWHLUACTX01 SQL Query Analyzer 4592 SELECT HL01 DBAdmin 005056AF21CE TCP/IP HL01\DBAdmin 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0

    60 5040 60 0x0404 0 LATCH_EX PARALLEL_PAGE_SUPPLIER (31AA0DD4) 22 1 343 86159 0 2009-06-23 14:14:43.243 2009-06-23 14:23:08.590 2 0 sleeping 0x010500000000000515000000C9D91AA528AE7CF33130568B4A06000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MYUKVWHLUACTX01 SQL Query Analyzer 4592 SELECT HL01 DBAdmin 005056AF21CE TCP/IP HL01\DBAdmin 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0

    60 4688 60 0x0404 0 LATCH_EX PARALLEL_PAGE_SUPPLIER (31AA0DD4) 22 1 594 86159 0 2009-06-23 14:14:43.243 2009-06-23 14:23:08.590 1 0 sleeping 0x010500000000000515000000C9D91AA528AE7CF33130568B4A06000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MYUKVWHLUACTX01 SQL Query Analyzer 4592 SELECT HL01 DBAdmin 005056AF21CE TCP/IP HL01\DBAdmin 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0

    ==========

    Any body having any IDEA???

    Just and FYI... I'm doing this exercise to know the scenarios why and when a spid will be having multiple entries in sysprocesses table.

    Thanks,

    Rohit

  • June 23, 2009 at 7:49 am

    #1013930

    The clue is in this bit

    LATCH_EX PARALLEL_PAGE_SUPPLIER

    There are multiple entries because SQL Server is running a Parallel query across more than 1 cpu.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply