DeadLock --trying to figure out what resource-list is telling me?

Question

Post reply

DeadLock --trying to figure out what resource-list is telling me?

456789psw

SSCarpal Tunnel

Points: 4728
More actions
May 15, 2018 at 9:13 am

#344557

trying to figure out what resource-list is telling me? I get the ownerd id, but I am not seeing what the issue...normally I would see an X, SX,UX or so forth that would tell me why the deadlock occurred. The victim is inserting rows into a temp table. The non victims are the same statement "inset into temp table over and over"
<resource-list>
    <SyncPoint>
      <owner-list>
        <owner id="process215a90fb468" />
        <owner id="process1f287a0c8c8" />
        <owner id="process21aa4037088" />
        <owner id="process2de006d5088" />
        <owner id="process1f059c91c28" />
        <owner id="process2dcb629cca8" />
        <owner id="process21aa4529468" />
        <owner id="process2ba90171468" />
        <owner id="process21760c80108" />
        <owner id="process21aa4fb1468" />
        <owner id="process1e1f3305468" />
      </owner-list>
      <waiter-list>
        <waiter id="process20071b76108" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process215a90fb468" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process1f287a0c8c8" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process21aa4037088" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process2de006d5088" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process1f059c91c28" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process2dcb629cca8" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process21aa4529468" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process2ba90171468" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process21760c80108" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process21aa4fb1468" />
      </waiter-list>
    </SyncPoint>
    <SyncPoint>
      <owner-list>
        <owner id="process20071b76108" />
      </owner-list>
      <waiter-list>
        <waiter id="process1e1f3305468" />
      </waiter-list>
    </SyncPoint>
</resource-list>
</deadlock>

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 1

Here is the whole output...maybe this might help

<victim-list>

</victim-list>

<process-list>

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process21aa4f91088" taskpriority="0" logused="20000" waittime="2566" schedulerid="14" kpid="47908" status="suspended" spid="94"

sbid="0" ecid="9" priority="0" trancount="0" lastbatchstarted="2018-05-14T16:12:29.507" lastbatchcompleted="2018-05-14T15:55:49.453" lastattention="1900-01-01T00:00:00.453" hostpid="5716" isolationlevel="read committed (2)" xactid="824619839" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item, a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID, case whena15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process21aa4fa9c28" taskpriority="0" logused="20000" waittime="2566" schedulerid="17" kpid="92604" status="suspended" spid="94"

sbid="0" ecid="10" priority="0" trancount="0" lastbatchstarted="2018-05-14T16:12:29.507" lastbatchcompleted="2018-05-14T15:55:49.453"

lastattention="1900-01-01T00:00:00.453" hostpid="5716" isolationlevel="read committed (2)" xactid="824619839" currentdb="5"

lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process21aa4fa1c28" taskpriority="0" logused="20000" waittime="2566" schedulerid="16" kpid="99980" status="suspended" spid="94"

sbid="0" ecid="11" priority="0" trancount="0" lastbatchstarted="2018-05-14T16:12:29.507" lastbatchcompleted="2018-05-14T15:55:49.453"

lastattention="1900-01-01T00:00:00.453" hostpid="5716" isolationlevel="read committed (2)" xactid="824619839" currentdb="5"

lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">

unknown </frame>

</executionStack>

insertinto #T8GX1Y9EDOL008

selectdistinct a14.ITM_ID Item,

a14.SUBCAT_ID SUBCAT_ID,

a12.STORE_ID STOREID,

case when a15.STORE_COMP_FLG = 'Y' then 'Same Store' else 'New Store' end SAME_STORE_FLG,

a12.DIV_ID DIV_ID,

a14.DEPT_ID DepartmentID,

a14.CAT_ID CAT_ID,

a15.CAL_DT BUSINESSDAY

from VIEWS.STORE_COMP a15

join VIEWS.STORE_DIM a12

on (a15.STORE_ID = a12.STORE_ID)

join VIEWS.STORE_ITM_DEPT a14

on (a15.STORE_ID = a14.STORE_ID)

where (((a12.STORE_ID)

in (select s22.STORE_ID

from VIEWS.STORE_GRP_STORE_DIM s22

where s22.STORE_GRP_ID in (30591, 30600)))

anda14.DEPT_ID not in (3022, 3027, 3004, 3020, 3021, 3025, 3026, 3045, 3046)

anda14.SUBCAT_ID in (1443)

anda15.CAL_DT between '2017-10-01' and '2017-12-30'

anda15.CAL_DT <= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</SyncPoint>

</resource-list>

</deadlock>

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

I could be wrong (because I can't see the code in the views) but I believe I can summarize the source of the problem in 4 words... "Distinct", "Views", and "Joined Views".

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 3

Solved!

<SyncPoint> is an indication of an Exchange event. If you run profiler trace with deadlock graph it will clearly show its and exchange dead lock( intra-query parallelism deadlocks). Add MAXDOP 1 and the issue will be resolved

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

456789psw - Wednesday, May 16, 2018 7:17 AM
Solved!
<SyncPoint> is an indication of an Exchange event. If you run profiler trace with deadlock graph it will clearly show its and exchange dead lock( intra-query parallelism deadlocks). Add MAXDOP 1 and the issue will be resolved

Not solved. You found a "patch". The use of "DISTINCT" is still a problem and indicates that the criteria is insufficient to prevent duplication of rows. Solve that and get the amount of time and resources being used by the query down and that will be the beginning of a true solution.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 5

you are correct I was really trying to solve the mystery of resource-list why it showed nothing. Fixing the underlying issue is something all together different. It just so happens the SQL is coming from Microstragey its more or less dynamic.

Microsoft says 2016 Sp2 is suppose to help...I shall cross my fingers.

https://support.microsoft.com/en-us/help/4089473/better-intra-query-parallelism-deadlock-troubleshooting-in-sql-server2