Ariadne (1/9/2009)
I've got a 64 bit, cluster, enterprise 2005 server that is having CPU issues. I can diagnose the standard stuff, but I'm seeing something that is strange. When the users kick off this one stored procedures which all it does is run a SELECT statement against an itty bitty 77 row table. Now the where clause is hideous, but that's another story (maybe).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?
Thanks,
Ariadne
I've seen SELECTs on such "itty-bitty" tables paralize all the CPU's on a server because of things like accidental cross joins (inner joins that resolve to many-to-many joins). Post you're query and attach the information requested in the 2nd link in my signature line below and let's have a go at it. Supressing parallelism for a query like this is like putting a bandaid on a stab wound. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.