sp_who2 results show one spid 24 times

  • Ariadne

    SSCrazy

    Points: 2044

    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

  • SQLBOT

    SSCrazy Eights

    Points: 8014

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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, MVP, M.Sc (Comp Sci)
    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
  • Ariadne

    SSCrazy

    Points: 2044

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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, MVP, M.Sc (Comp Sci)
    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
  • Balaji L

    Hall of Fame

    Points: 3067

    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?

  • parthi-1705

    SSCrazy Eights

    Points: 9272

    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

  • JeremyE

    SSCoach

    Points: 15038

    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

  • Jeff Moden

    SSC Guru

    Points: 993628

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Leo.Miller

    SSChampion

    Points: 12820

    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

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Please note, original thread is a year old.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply