November 12, 2008 at 8:59 am
we were doing load testing yesterday and the team lead did a sp_who2 and then he went and checked something in server logs and told dat SPID 52 is using lot of connections and the query has to be tuned?...im still wondering on what basis did he say that..plz someone explain me..
another question is when u do sp_who2...can we judge about a qurey based on "CPU time" and"DISK I/O"...like is there any threshold value for it..thanks..
November 12, 2008 at 9:24 am
A particular SPID typically is on one connection. A user can be connected many times, but each process has it's own ID.
SPIDs are reused, so you can see them in there a lot.
November 12, 2008 at 9:46 am
so u mean to say that if i see the SPIDs repeating many times then it means that the process for that id has lot of connections..right...
November 12, 2008 at 10:24 am
chances are spid was running a parallel query. If waittype was CXPacket then thats definitely the case.Happens quite often if processing a lot of data.
Doesn't mean its a bad thing, optimiser has just decided to run it as a parallel query as it above the cost threshold for parallelism.
Check if this query would be expected to return a lot of data. If not check indexing is ok.
---------------------------------------------------------------------
November 12, 2008 at 10:47 am
thanks...
December 2, 2008 at 4:01 pm
As a best practise
configure maxdop on half your cores
so if oyu have 8 cores in the server make maxdop 4 or less.
If you have hyperthreaded CPU's ..turn of hyperthreading and then configure maxdop..
do not allow maxdop to cause excessive CPU overhead and add strain to UMS pool.
maxdop is nice but should be avioded for oltp application concepts you want fast transactions , unless this is a dss or olap solution then degree of parralellism could be halpfull.
December 3, 2008 at 2:28 am
What about setting the cost threshold for parallellism at server level?
Where is the reference to your suggestions?
N 56°04'39.16"
E 12°55'05.25"
December 3, 2008 at 2:36 am
Hi
Yes you can set the cost threshold , but even when you do initiate a degree of parrallelism opertion you do not want to effect all the schedulers and CPU's because of a query that executes in parralell.
http://support.microsoft.com/kb/329204/
these are just general guidlines and actually comes down to tetsing your production workload with different maxdop settings.
December 3, 2008 at 2:49 am
I still can't see the reference to use only half the maximum number of processórs.
N 56°04'39.16"
E 12°55'05.25"
December 3, 2008 at 3:33 am
I think the reference gives you clear indication as to why you need to do it.
Just picture this ..8 way machine with high workloads and many statements initiating a degree of parralellism. So all of a sudden you have high CPU usage due to these statements. The queries with a serial plan is suffering because of these statements so the impact to CPU utilization is much more by configuring maxdop to use all. By limiting the amount you force all parralel queries to only use specific amount of CPU's , this with 2005's new thread scheduling model the serial queries (by rights) should not be scheduled on those schedulers (not guaranteed) as the rest of the CPU's that is not being used for degree of parralelsm will have low usage and the UMS will rather schedule worker threads of the serial queries on them.
There is no gospel on what the setting should be , but out of personal experience and proper unit testing I have settled on always half the cores or half the physical cpu's and I am sure that many would agree , maybe not to the excat number but to the concept and the idea.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply