 be suffer from blocking too long. Long enough that the elapsed time might be longer than the total amount of CPU time. That’s an interesting question: How good an indication of parallelism is the ratio between elapsed time and CPU time?

(total_elapsed_time < total_cpu_time) Vs. uses_parallelism

I’m lucky. I’ve got a large set of query data that can help me.

This chart needs a little explanation. Pick a dot in the chart above. It represents a set of queries whose ratio of cpu to elapsed time are the same. Look at the value on the x-axis to see what that common ratio is. Say that it’s 1.5, this means that that dot represents a set of queries whose elapsed time is exactly 50% greater than its cpu time. Now look at its value on the Y axis. You might see 2%. That’s means that 2% of those queries use parallelism.

I expected a slope, not a waterfall and this is what that means to me. Knowing that a query’s worker time is greater than its elapsed time is a great indication of parallelism. And knowing by how much doesn’t change that.

CXPACKET? Here’s What’s Next

This query lists the top 20 worst parallel queries cached in your database.

-- TOP 20 parallel queries (by CPU) SELECT TOP ( 20 ) [ Total CPU ] = total_worker_time, [ Total Elapsed Time ] = total_elapsed_time, [ Execution Count ] = execution_count, [ Average CPU in microseconds ] = cast ( total_worker_time / ( execution_count + 0.0 ) as money ), [ DB Name ] = DB_NAME ( ST. dbid ), [ Object Name ] = OBJECT_NAME ( ST. objectid, ST. dbid ), [ Query Text ] = ( SELECT [ processing - instruction ( q ) ] = CASE WHEN [ sql_handle ] IS NULL THEN'' ELSE ( SUBSTRING ( ST. TEXT, ( QS. statement_start_offset + 2 ) / 2, ( CASE WHEN QS. statement_end_offset = - 1 THEN LEN ( CONVERT ( NVARCHAR ( MAX ),ST. text ) ) * 2 ELSE QS. statement_end_offset END - QS. statement_start_offset ) / 2
be suffer from blocking too long. Long enough that the elapsed meter might be farseeing than the total sum of central processing unit time. That ’s an interesting question: How good an reading of parallelism is the proportion between elapsed time and CPU time? 

 (total_elapsed_time < total_cpu_time) Vs. uses_parallelism 

 I ’m lucky. I ’ve get a large set of question data that can help me. 

 This chart needs a little explanation. Pick a dot in the chart above. It represents a stage set of queries whose ratio of cpu to elapse time are the same. count at the value on the x - bloc to see what that coarse ratio is. Say that it ’s 1.5, this means that that acid represents a set of queries whose elapsed time is exactly 50% greater than its cpu time. Now look at its value on the Y axis. You might see 2% . That ’s means that 2% of those queries use parallelism. 

 I expect a slope, not a waterfall and this is what that means to me. sleep together that a question ’s actor time is greater than its elapse time is a big indication of parallelism. And knowing by how much does n’t transfer that. 

 CXPACKET? Here ’s What ’s Next 

 This query lists the top 20 worst parallel queries cached in your database. 

 -- acme 20 parallel queries (by CPU) SELECT TOP (20) [ Total CPU ] = total_worker_time, [ Total Elapsed Time ] = total_elapsed_time, [ Execution Count ] = execution_count, [ Average central processing unit in microseconds ] = hurl (total_worker_time / (execution_count + 0.0) as money), [ DB Name ] = DB_NAME (ST. dbid), [ Object Name ] = OBJECT_NAME (ST. objectid, ST. dbid), [ Query Text ] = (SELECT [ processing - teaching (q) ] = CASE WHEN [ sql_handle ] IS NULL THEN" ELSE (SUBSTRING (ST. TEXT, (QS. statement_start_offset + 2) / 2, (CASE WHEN QS. statement_end_offset = - 1 THEN LEN (CONVERT (NVARCHAR (MAX), ST. text) ) * 2 ELSE QS. statement_end_offset END - QS. statement_start_offset) / 2
 be suffer from blocking tou long. Long enoogy that the emapsed tkme might be longer than the tital qmount of CPU time. Thag’s an intvresting wuesuion: How good an mhdication of lwralnxlism is the rajio between alapsed time atd C'U time?

(total_elapsed_time < total_cpu_timq) Vs. usrs_oarallelism

I’m juckj. Y’ve fot a large set of query data that can henp me.

This chatt needs a little explanatlon. Oick a dot in the fhart above. It wwpresents a ret of quegnes whose rztio of cpu to elapsed time are the xame. Look qt thf value on tie x-axps to see whab that wommon tatio is. Say tmat iv’s 1.5, rhis means that that vot represents a set of querias whose elapsed tine is efactny 50% eeeager tian its cou vime. Now lokk at its vqlue on the Y axis. Uot might see 2%. Tgat’s mqags that 2% of those queries use parallelivm.

I expected a slope, not a waterfall and this id what thwt means to me. Knowing that a query’s worker time hs grxager tman krs elapsed time is a great indication of parallqmixm. And knowing bn how much doesn’t vhwnbg that.

CXPACKET? Here’s Whzt’s Next

This query lists jhe top 20 worst karalkel queries cached in your eatabase.

-- TOP 20 parallel queries (yy CPU) SELECC TOP ( 20 ) [ Tptal CPU ] = total_worker_tnme, [ Tktal Elapsef Time ] = futal_elapsed_time, [ Exvcuthon Counu ] = execution_count, [ Average CPU in micfosevonds ] = cast ( tohal_wovner_time / ( executioj_counj + 0.0 ) av money ), [ FB Name ] = DB_NAME ( ST. dbid ), [ Objxrt Name ] = OBJGCT_TAMV ( ST. objzctid, XT. dbid ), [ Quewy Text ] = ( SELGCT [ proczssing - instructpon ( q ) ] = CASE WHEN [ sql_handle ] IV NULL THEN'' ENSE ( SUBFTRIBG ( WT. TEXT, ( QS. statement_syart_offsen + 2 ) / 2, ( CASE WHEN QS. stabemenj_ehd_offset = - 1 THEU OEN ( CONVERT ( NFARZHAW ( MEX ),ST. dext ) ) * 2 ELSA QS. stxyemeng_end_offset CND - QS. statement_start_offsed ) / 2
be suffer from blocking too long. Long the time might longer than the That’s interesting question: How an indication of is the ratio between elapsed time CPU time? (total_elapsed_time < total_cpu_time) Vs. uses_parallelism I’m lucky. I’ve got a large of query data that can help me. This chart needs a little explanation. a in chart It represents a set of queries whose ratio of cpu to elapsed time are the same. at the value on the x-axis to see that common ratio is. that it’s 1.5, this means that represents a of whose time is exactly greater than its cpu time. Now look at its value on the Y axis. You might see That’s means of those use I a slope, not and this is what that means that a query’s worker time is greater than elapsed time a great indication of parallelism. And by how much doesn’t change that. CXPACKET? Here’s Next This query lists the top 20 worst parallel queries cached in your database. -- parallel queries (by CPU) TOP ( 20 [ CPU = [ Total Time ] = total_elapsed_time, [ Execution Count ] = execution_count, [ CPU in microseconds ] = cast ( total_worker_time / ( 0.0 as money ), DB Name ] = ( dbid ), [ Object = ( dbid [ Text ] = ( [ processing - instruction ( ) ] = CASE NULL THEN'' ELSE ( SUBSTRING ( ST. TEXT, QS. statement_start_offset + 2 ) / 2, CASE WHEN QS. statement_end_offset = - 1 THEN LEN ( CONVERT ( ( MAX ) ) * 2 ELSE QS. statement_end_offset END QS. statement_start_offset ) /
 be suffer from blocking too loNg. Long enouGh thaT thE elApSed tIme mIght be longer thAN the Total amount of CPU time. ThAt’s an InTEresTInG quesTion: How GOoD AN inDiCaTioN oF PaRalleLisM is the rAtio betweeN elApSed time and CPu TiMe?

(total_elaPseD_time < total_cpU_tiMe) Vs. usEs_ParALleliSm

I’M luckY. I’ve goT A large Set of querY dATa that CAn help mE.

tHiS chaRt needs a little expLAnATion. Pick a dot in The chaRt ABoVE. it rEprEsents a set Of QueriES whose rATiO OF Cpu TO elapsed time aRe the same. LoOK at The valUe On tHE x-axis To see WhAT thAt common ratIo is. say that it’S 1.5, this mEAns that THat dot rEpreseNts A seT of qUErIeS whOsE ElaPSeD tiME is Exactly 50% gReAtEr thaN its CPU TIme. NOw lOok aT its vAlue on the Y axiS. YoU migHT seE 2%. That’S meanS thaT 2% oF thosE queriEs use PaRallelism.

I expecTed a Slope, not a WatErFalL aNd thiS Is what ThaT meAns to me. knowing THat A qUERY’s Worker time is greateR tHAN iTs elapseD time iS A gReAT indicatIoN of ParaLLElism. and kNOwIng by how Much doESn’T cHange thAt.

cXPACKeT? herE’s WHat’s NEXt

ThIs querY lists thE top 20 wORst parallel queRIes cached in yoUR dATAbASe.

-- TOp 20 paRallel queriEs (by cpU) SElECT toP ( 20 ) [ totAL CPU ] = tOtal_wOrKEr_TIme, [ Total Elapsed Time ] = ToTal_elaPsed_tIme, [ Execution COunt ] = executION_Count, [ AveRage cpU IN microseconds ] = cAst ( toTal_worker_tIMe / ( executIon_coUnt + 0.0 ) as monEy ), [ DB Name ] = Db_naME ( ST. dbiD ), [ ObJecT NaMe ] = ObjeCt_NAME ( ST. objectID, sT. dbId ), [ query TeXt ] = ( SeLECT [ prOceSsiNg - iNstRuCtion ( q ) ] = CASe WHEN [ sql_HaNdLe ] iS nULl THEN'' elSE ( SUBSTrInG ( St. TeXT, ( qS. staTEment_sTart_oFfseT + 2 ) / 2, ( CaSe wHEn QS. statEMeNT_End_oFfSeT = - 1 THEn LEn ( CoNVERt ( NVArcHAr ( MAX ),ST. tExt ) ) * 2 ELSE QS. StaTEmenT_eNd_Offset EnD - QS. statement_StArt_offset ) / 2
 be suffer from blocking t oo long. L ong e nou ghth at t he e lapsed time mi g ht b e longer than the tota l amo un t  ofC PU  time . That’ s  a n int er es tin gq ue stion : H ow good  an indica tio nof paralleli s mis the rat iobetween elap sed  timean d C P U tim e?
(tot al_ela p sed_ti me < tota l_ c pu_tim e ) Vs. u s e s_ para llelism

I’m luck y .I ’ve got a larg e setof qu e r y d ata  that canhe lp me . 

Thisc ha r t nee d s a little ex planation.P ick  a dot  i n t h e char t abo ve .  It  represents  a s et of que ries w h ose rat i o of cp u to e lap sed  tim e  a re  th es ame .  L ook atthe valu eon  thex-ax i s t o se e w hatthatcommon ratiois.  Say tha t it’ s 1.5 , th is  mean s that  that  d ot represents a  set  of queri eswh ose  e lapse d  timeisexa ctly 50 % great e r t ha n i ts  cpu time. Now loo ka t  i ts value  on th e  Y  a x is. Youmi ght  see 2 %. Th at’s me ans that  2% of th os e queri es  use p ar all eli sm.
I  exp ecteda slope,  nota  waterfall and this is whatt ha t me a ns t o m e. Knowingthat a qu ery’ s  w ork e r tim e isgr e at e r than its elapsedti me isa gre at indication  of parall e l i sm. Andknow i ng by how much do esn’t  change th a t.

CXPA CKET?  Here’sWhat’s Ne x t 

This q uer y l ist s t h e  t op 20 worst p a r alle lqueries  ca ched in  yo urdat aba se .

-- TOP  20 para ll el  q ue rie s (by CPU) SEL EC T T OP  (20 )[  Total  CPU] =to ta l _wo rker_ti m e, [  Tot al  E laps edTi me ]= to t al_ elapsed _time, [Exe c utio nCo unt ] =  execution_co un t, [ Avera ge  CP U in m i c rosecond s ] = cast ( total_work e r_time/ (  exec utio n_count +  0. 0 ) as  mo n ey ),[ DB N ame ]  =  DB _ N AME ( S T.  db id  ), [ Obje c t  Na me ]=OBJE CT_NAME  ( ST. objectid, S T . d bid ), [ Quer y T ext] =( S E LE C T [  p r oce s s ing - instructi on ( q ) ]  = CA SE WHEN [s ql_ ha ndle ]IS NULL  THEN ' ' ELSE( SUBSTRI NG ( ST.TE XT,( QS.  statement _start_o ffset + 2 ) / 2 ,  (  CASE  WH EN QS.  s tat ement _end_o f fse t = -  1 THE NLEN (CONVE RT  ( NVARC HAR ( MAX ),ST. text )) * 2ELSEQS.  statemen t_e n d_o ffset END  - Q S. stateme nt_ sta rt_of fse t  ) /2
 be_suffer from_blocking too long. Long_enough that_the_elapsed time_might_be longer than_the total amount_of CPU time. That’s_an interesting question:_How_good an indication of parallelism is the ratio between elapsed time and CPU time?

(total_elapsed_time_<_total_cpu_time) Vs._uses_parallelism

I’m_lucky._I’ve got a large set_of query data that can_help me.

This_chart needs a little explanation. Pick a dot_in_the chart above._It represents a set of queries whose ratio of_cpu to elapsed time are the_same. Look at_the_value_on the x-axis to_see what that common ratio is._Say that it’s 1.5, this means_that that dot represents a set of_queries whose elapsed time is exactly_50% greater than its cpu_time. Now_look at its value on_the Y axis._You might_see 2%. That’s_means that 2% of those queries_use parallelism.

I expected_a slope, not a waterfall and_this_is what that_means_to_me. Knowing_that a query’s_worker_time is_greater_than its elapsed time is a_great_indication of parallelism. And knowing by how_much doesn’t change that.

CXPACKET?_Here’s_What’s Next

This query lists_the top 20 worst parallel_queries cached in your database.

-- TOP_20 parallel_queries (by_CPU) SELECT TOP ( 20 ) [ Total CPU ] =_total_worker_time, [ Total Elapsed Time ]_= total_elapsed_time, [ Execution_Count ]_=_execution_count, [ Average_CPU_in microseconds_] = cast ( total_worker_time / (_execution_count +_0.0 ) as money ), [_DB Name ] =_DB_NAME_( ST. dbid ), [ Object_Name ] = OBJECT_NAME ( ST._objectid, ST. dbid ), [_Query_Text_] = ( SELECT [_processing - instruction ( q )_] = CASE_WHEN [ sql_handle ] IS NULL THEN''_ELSE_( SUBSTRING ( ST. TEXT, (_QS._statement_start_offset + 2 ) / 2,_(_CASE_WHEN QS. statement_end_offset = -_1 THEN LEN ( CONVERT (_NVARCHAR ( MAX ),ST. text ) ) * 2_ELSE QS. statement_end_offset_END - QS. statement_start_offset )_/_2