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

@@SPID gives you the spid for the current connection.

From the query analyzer itself I know the spid for my connection, my question is how come same spid having more then one entry in sysprocesses table.

If you are asking about multiple rows for same spid when you run sp_who2, then what you are seeing is parallelism. If you are asking about multiple entries in sysprocesses, the spid number can be reassigned to another connection once the original connection is closed and disposed.

Thx for your responses....

I got it... it was because of maxdop option set to 0 and this means use all the available processors for parallelism...... and when this happens you'll see more than one entry for the same spid in sysprocesses table till the time my query is running. and this is fine and not at all a abnormal behaviour of sql server which I thought is.

rohit2900:

Thx for your responses....

I got it... it was because of maxdop option set to 0 and this means use all the available processors for parallelism...... and when this happens you'll see more than one entry for the same spid in sysprocesses table till the time my query is running. and this is fine and not at all a abnormal behaviour of sql server which I thought is.

 thats correct.. MADXOP is defaulted to 0 and parallelism is normal. Unless you see queries taking very long time and you see that some processors are waiting for others to finish (you will see this on large servers with 8-16 processors or more) you dont need to set it to anything else...