January 9, 2009 at 2:02 pm
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
January 9, 2009 at 3:53 pm
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
January 10, 2009 at 1:02 am
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
January 12, 2009 at 11:51 am
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
January 12, 2009 at 12:52 pm
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
August 27, 2010 at 2:22 pm
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?
August 27, 2010 at 2:57 pm
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
August 27, 2010 at 4:01 pm
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.
August 28, 2010 at 12:59 pm
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.
August 29, 2010 at 4:01 pm
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:
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.
August 31, 2010 at 1:33 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply