performance - MS SQL Server Actual CPU Cost per physical operation -

i have query executing on microsoft sql server. here query , execution plan: https://www.brentozar.com/pastetheplan/?id=bjurhrwng

the query extremely slow:

table 'worktable'. scan count 30, logical reads 184041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'siteversions'. scan count 1, logical reads 363, physical reads 0, read-ahead reads 351, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table '#bb50937f'. scan count 1, logical reads 5979, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'allwebs'. scan count 11, logical reads 3041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'allsites'. scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  (1 row(s) affected)   sql server execution times:    cpu time = 715296 ms,  elapsed time = 873728 ms. sql server parse , compile time:     cpu time = 0 ms, elapsed time = 0 ms. 

this on dual intel(r) xeon(r) cpu e5-2680 0 @ 2.70ghz, 2700 mhz, 8 core(s), 16 logical processor(s) system. query executes max-dop of 1.

the actual execution plan indicates specific sort operation has largest estimated cost , there no operations return extremely large number of rows.

however, when examine actual execution plan, find unlikely sort operation causes cpu load. e.g., when execute sort operation in separate stand-alone query, fast.

i suspect merge join of being performance killer instead: joins 2 averagely large rowsets (29840 , 9557 rows, resp.), resulting in potentially large number of joined rows. has not restrictive clause , expensive residual operation several functions need evaluated each residual row. however, can't support suspicion actual numbers.

therefore, have few questions can validate suspicion:

• is there way actual cpu cost reported per physical operation sql server?
• is there way report on actual number of residual operations executed and/or number of rows remain after clause in merge join operation?
• is there way estimate cpu cost associated 184041 reported logical reads on 'worktable'?
• is there way find out physical operation uses 'worktable'

regards, beat

i wouldn't worry cpu costs seeing, merely indicator , can give idea how pressure cpu getting query. in case think cpu cost caused complexity of query, rank() in subquery , functions in clause.

i try , split query in few logical parts, in order query optimizer figure out optimal plan , improve it's work intermediate results:

    select  siteid           , webid           , versionid           , version           , rank() on (partition siteid, webid, versionid order id desc) rank        #tempsiteversions        siteversions;      ctefirstrankingsites     (         select  sv.siteid               , s.platformversion               , sv.version               , ssv.applicablewebtemplate               , w.webtemplate               , ssv.previoustargetversionnumber               , ssv.targetversionnumber            #tempsiteversions sv                 join tvf_webs_nolock_all() w on w.siteid = sv.siteid                 join sites s (nolock) on w.siteid = s.id                 join @sitesequenceversions ssv on sv.versionid = ssv.id           sv.rank = 1     )     select distinct             siteid        [ctefirstrankingsites] frs       (              (               (dbo.fn_convertversiontonumber(frs.platformversion) < 15000000000000)               , (dbo.fn_convertversiontonumber(frs.version) < frs.previoustargetversionnumber)              )              or (                  (dbo.fn_convertversiontonumber(frs.platformversion) >= 15000000000000)                  , (dbo.fn_convertversiontonumber(frs.version) < frs.targetversionnumber)                 )             )             , (                  frs.applicablewebtemplate = 0                  or frs.applicablewebtemplate = frs.webtemplate                 ) 

there might syntax error in there don't have actual tables , functions work with, idea.