Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_who2 results show one spid 24 times


sp_who2 results show one spid 24 times

Author
Message
Ariadne
Ariadne
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
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



SQLBOT
SQLBOT
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47415 Visits: 44405
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
Ariadne
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
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



GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47415 Visits: 44405
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
Balaji L
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 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?
parthi-1705
parthi-1705
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 2196
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
JeremyE
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3725 Visits: 4029
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45432 Visits: 39942
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. :-P

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Leo.Miller
Leo.Miller
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 1501
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search