sp_who2 results show one spid 24 times
What I’m trying to figure out is why only that sproc yields 24 rows per spid that runs this sproc? Is sql deciding to do this? or did the developer put some multi-threaded stuff in? This sproc when ran several times at the same time, causes all kinds of CXPacket waits (I scaled parallelism back to 6 from 0 and that helped the CPUs overall, but did not cut down on CXPacket waits). All the other code issued against the database appears to only have 1 row in the sp_who2 results set. Can anyone give me some insight into this?
Ans: It’s parallelism. It’s something that SQL decides to do, not the developer.
Not all of the threads will likely be running and there can be more than the maxdop set or the number of cores. Maxdop sets the max parallelism per operator, so if the maxdop (or no of cores) is 8, you can have a couple of operators not running (each with 8 threads) and then one that is running (also with 8 threads)
(I scaled parallelism back to 6 from 0 and that helped the CPUs overall, but did not cut down on CXPacket waits) ==> Wrong way. 0 means that queries can parallel across all cores. How many processing cores do you have? OLTP or datawarehouse type app?