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 2, 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: Tuesday, June 24, 2014 8:22 AM
Points: 771, Visits: 1,729
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 2, 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 @ 1:14 AM
Points: 42,412, Visits: 35,479
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 2, 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: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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 2, 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: Tuesday, June 24, 2014 8:22 AM
Points: 771, Visits: 1,729
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 2, 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 @ 1:14 AM
Points: 42,412, Visits: 35,479
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 2, 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: Tuesday, June 24, 2014 8:22 AM
Points: 771, Visits: 1,729
Got it. Brilliant stuff.

Thank you very much Gila.
Post #1490657
Posted Monday, September 2, 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: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
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."

(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