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

high cpu and parallelism Expand / Collapse
Author
Message
Posted Monday, September 02, 2013 8:58 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:14 PM
Points: 746, Visits: 1,702
Hi Everyone,

I was just doing a read-up upon high-cpu and CXPACKET wait/parallelism.

One thing, which is over my reach is below statement.

"worker time is greater than its elapsed time is a great indication of parallelism"

Can anyone please explain an example how could it be possible cpu> total elapsed time. How this is possible? Again i am just learner and I wanted to know who this can happen in real time?

Looking for brief explanation on this.

Thanks in Advance
Post #1490645
Posted Monday, September 02, 2013 9:06 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:59 AM
Points: 41,508, Visits: 34,425
Query parallels, spends 10 seconds executing on 4 CPUs. Elapsed time, 10 seconds. CPU time used 10 seconds * 4 CPUs = 40 seconds.


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 #1490646
Posted Monday, September 02, 2013 9:10 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:57 AM
Points: 704, Visits: 3,270
You no longer MCM?!

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1490647
Posted Monday, September 02, 2013 9:13 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:14 PM
Points: 746, Visits: 1,702
Thanks Gila for quick response.

one more clarification, in that case, SQL doesn't SUM up all the execution times for all the CPU's as it is one query which is splitted into parallel threads and will be the Total elapsed time of the actual query?
Post #1490648
Posted Monday, September 02, 2013 9:26 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:59 AM
Points: 41,508, Visits: 34,425
Elapsed time is literally that. It's the actual, real time that elapsed while the query was running. Measured by taking the actual real time that the query started, the actual, real time that the query finished and taking the difference.

If a query started executing at 10:00:02 and completed at 10:00:05, then it took 3 seconds to run, the elapsed time is 3 seconds. If that query paralleled 4 ways (ran on 4 CPUs) for all 3 of those seconds, then the total CPU time used by that query was 12 seconds



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 #1490651
Posted Monday, September 02, 2013 9:34 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:14 PM
Points: 746, Visits: 1,702
Got it. Brilliant stuff.

Thank you very much Gila.
Post #1490657
Posted Monday, September 02, 2013 6:25 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
Abu Dina (9/2/2013)
You no longer MCM?!


They're cancelling the MCM cert.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1490720
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse