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.

    ==========

    60387600x02087250CXPACKET 221558138615902009-06-23 14:14:43.2432009-06-23 14:23:08.59000sleeping 0x010500000000000515000000C9D91AA528AE7CF33130568B4A06000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000MYUKVWHLUACTX01 SQL Query Analyzer 4592 SELECT HL01 DBAdmin 005056AF21CETCP/IP HL01\DBAdmin 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000x01001600A2ABE328C0C0917600000000000000001236012524

    60752600x04040LATCH_EXPARALLEL_PAGE_SUPPLIER (31AA0DD4)2216728615902009-06-23 14:14:43.2432009-06-23 14:23:08.59040sleeping 0x010500000000000515000000C9D91AA528AE7CF33130568B4A06000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000MYUKVWHLUACTX01 SQL Query Analyzer 4592 SELECT HL01 DBAdmin 005056AF21CETCP/IP HL01\DBAdmin 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000x000000000000000000000000000000000000000000

    60491200x00000LATCH_EXPARALLEL_PAGE_SUPPLIER (31AA0DD4)2214688615902009-06-23 14:14:43.2432009-06-23 14:23:08.59030sleeping 0x010500000000000515000000C9D91AA528AE7CF33130568B4A06000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000MYUKVWHLUACTX01 SQL Query Analyzer 4592 SELECT HL01 DBAdmin 005056AF21CETCP/IP HL01\DBAdmin 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000x000000000000000000000000000000000000000000

    605040600x04040LATCH_EXPARALLEL_PAGE_SUPPLIER (31AA0DD4)2213438615902009-06-23 14:14:43.2432009-06-23 14:23:08.59020sleeping 0x010500000000000515000000C9D91AA528AE7CF33130568B4A06000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000MYUKVWHLUACTX01 SQL Query Analyzer 4592 SELECT HL01 DBAdmin 005056AF21CETCP/IP HL01\DBAdmin 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000x000000000000000000000000000000000000000000

    604688600x04040LATCH_EXPARALLEL_PAGE_SUPPLIER (31AA0DD4)2215948615902009-06-23 14:14:43.2432009-06-23 14:23:08.59010sleeping 0x010500000000000515000000C9D91AA528AE7CF33130568B4A06000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000MYUKVWHLUACTX01 SQL Query Analyzer 4592 SELECT HL01 DBAdmin 005056AF21CETCP/IP HL01\DBAdmin 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000x000000000000000000000000000000000000000000

    ==========

    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