Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sp_who2 results show one spid 24 times Expand / Collapse
Author
Message
Posted Friday, January 9, 2009 2:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 19, 2012 4:00 PM
Points: 100, Visits: 168
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



Post #633869
Posted Friday, January 9, 2009 3:53 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
I'm pretty sure this is a negative result of parallelism.

I think setting your server-level MAXDOP to 0 means that you're letting SQL Server manage it instead of specifying a value. I might be wrong on that. My guess is that you have more than 26 cores.

you could also ask them to specify the MAXDOP query hint.

more info here on that setting: http://msdn.microsoft.com/en-us/library/ms189094(SQL.90).aspx


~BOT


Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #633971
Posted Saturday, January 10, 2009 1:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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)

Ariadne (1/9/2009)
(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?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #634112
Posted Monday, January 12, 2009 11:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 19, 2012 4:00 PM
Points: 100, Visits: 168
The process that is having the CXPacket problem is a SELECT statement with a hideous Where Clause. I'm not particularly sure that this is what's taking up all the CPU, as I said, but since the server is getting hammered, thought I'd try scaling back parallelism. As I said, seems to alleviated some of the CPU pressure, and the users aren't complaining quite so loudly, but I would really like to figure out what I'm actually seeing inside sp_who2 results. For instance, doesn't the 25 lines in the results set equate to how many threads SQL uses to complete the query? Or does it mean something else?

Gila: This is an OLTP system if that helps any. Found out the server have AMD 2-socket 4-core 2210 mhz processors resulting in 8 CPUs total.

Thanks to you both for helping!

Lezza



Post #634908
Posted Monday, January 12, 2009 12:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
Ariadne (1/12/2009)
Gila: This is an OLTP system if that helps any. Found out the server have AMD 2-socket 4-core 2210 mhz processors resulting in 8 CPUs total.


If it's OLTP then that means lots of small queries. I'd set maxdop to 4 to start with, and consider dropping it to 2 if there were still CXPacket problems. Oh, and take that nasty where clause apart and rewrite it.

If you're looking for the worst CPU users, run profiler or a server-side trace for an hour or so, trace the T-SQL Batch completed and RPC:completed events, once it's done, save then results to a table on another server and look for the queries with the highest value for CPU.

For instance, doesn't the 25 lines in the results set equate to how many threads SQL uses to complete the query? Or does it mean something else?


In total, yes, though not all of those 25 will be running at once.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #634969
Posted Friday, August 27, 2010 2:22 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 3:13 AM
Points: 853, Visits: 271
sp_who returns only the spid which I am running the query. I can see many spid are opened but those are not listed. Whenever i run it returns only open spid. why it happens?
Post #976761
Posted Friday, August 27, 2010 2:57 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
hi,
I am also facing same porblem today when i run Sp_who or Sp_who2 it is returnning only current session id details,but if i run Sp_lock it is showing as [Msg 297, Level 16, State 1, Procedure sp_lock, Line 42 The user does not have permission to perform this action.] it is clear that it is some rights problem,but what is about sp_who/2 it is rights problem? both i am running in same (sessionid,server,db)

Thanks
Parthi


Thanks
Parthi
Post #976778
Posted Friday, August 27, 2010 4:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 2,964, Visits: 3,191
From BOL:

Permissions
--------------------------------------------------------------------------------

Requires VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server. Otherwise, the user sees only the current session.


http://msdn.microsoft.com/en-us/library/ms174313.aspx
Post #976813
Posted Saturday, August 28, 2010 12:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #976997
Posted Sunday, August 29, 2010 4:01 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 7:56 PM
Points: 450, Visits: 1,343
SQL is creating a parallel query, and this is independent of the size of the table. Without seeing the query and schema details etc. it's impossible to analyze much more than that.
Run the select in Management Studio and look at the execution plan (actual not estimated). If you post it to the forum some one will give you some pointers.

There are a number of issues with MAXDOP = 0 (or not = 1) on OLTP servers. One of them is the problem you are getting here. A quick fix is to either add a query hint of MAXDOP = 1 to the select, or set MAXDOP = 1 via sp_configure. This is a very contentious issue between dba's but here is a really good article:
http://sqlblog.com/blogs/aaron_bertrand/archive/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism.aspx

If you only have one problem query I'd add the query hint. If you have lots of problem queries I'd consider setting MAXDOP = 1. On an OLTP server, where you expect almost all queries to be well optimized and indexes to be well designed for performance your queries should run serialised anyway, as you would expect all queries to run under the cost threshold of 5. Setting MAXDOP = 1 prevents the odd poorly performing query from affecting the other queries.

This is not a decision to be made lightly and you need to consider all the options and TEST, TEST, TEST.

Cheers

Leo
Post #977122
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse