SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
456789psw
456789psw
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3228 Visits: 852
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>
456789psw
456789psw
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3228 Visits: 852
Here is the whole output...maybe this might help


<deadlock>

<victim-list>

<victimProcess id="process2ba90170ca8" />

</victim-list>

<process-list>

<process id="process2ba90170ca8" taskpriority="0" logused="20000" waittime="2562" schedulerid="7" kpid="17524" status="suspended" spid="94" sbid="0" ecid="5" 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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process21aa4529c28" taskpriority="0" logused="20000" waittime="2565" schedulerid="9" kpid="44816" status="suspended" spid="94" sbid="0" ecid="1" 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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process21aa4531088" taskpriority="0" logused="20000" waittime="2565" schedulerid="10" kpid="72404" status="suspended" spid="94" sbid="0" ecid="2" 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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process21aa4521088" taskpriority="0" logused="20000" waittime="2565" schedulerid="8" kpid="23084" status="suspended" spid="94" sbid="0" ecid="3" 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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process21aa4541468" taskpriority="0" logused="20000" waittime="2565" schedulerid="12" kpid="55436" status="suspended" spid="94" sbid="0" ecid="4" 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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process21aa4539c28" taskpriority="0" logused="20000" waittime="2565" schedulerid="11" kpid="99216" status="suspended" spid="94" sbid="0" ecid="6" 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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process2f262608ca8" taskpriority="0" logused="20000" waittime="2565" schedulerid="13" kpid="78476" status="suspended" spid="94" sbid="0" ecid="12" 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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process21aa4fb1c28" taskpriority="0" logused="20000" waittime="2566" schedulerid="18" kpid="52068" status="suspended" spid="94" sbid="0" ecid="8" 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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

<process id="process21aa4f99088" taskpriority="0" logused="20000" waittime="2566" schedulerid="15" kpid="83412" status="suspended" spid="94" sbid="0" ecid="7" 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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (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">

<executionStack>

<frame procname="adhoc" line="1" stmtend="1670" sqlhandle="0x02000000bcf69e241d6e10ae118fbd762b2b6e8154fd9bda0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

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 &lt;= (select max(cal_dt) from VIEWS.CAL_DT where CURR_SLS_DT_FLG ='Y')) </inputbuf>

</process>

</process-list>

<resource-list>

<SyncPoint>

<owner-list>

<owner id="process21aa4529c28" />

<owner id="process21aa4531088" />

<owner id="process21aa4521088" />

<owner id="process21aa4541468" />

<owner id="process21aa4539c28" />

<owner id="process2f262608ca8" />

<owner id="process21aa4fb1c28" />

<owner id="process21aa4f99088" />

<owner id="process21aa4f91088" />

<owner id="process21aa4fa9c28" />

<owner id="process21aa4fa1c28" />

</owner-list>

<waiter-list>

<waiter id="process2ba90170ca8" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process21aa4529c28" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process21aa4531088" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process21aa4521088" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process21aa4541468" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process21aa4539c28" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process2f262608ca8" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process21aa4fb1c28" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process21aa4f99088" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process21aa4f91088" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process21aa4fa9c28" />

</waiter-list>

</SyncPoint>

<SyncPoint>

<owner-list>

<owner id="process2ba90170ca8" />

</owner-list>

<waiter-list>

<waiter id="process21aa4fa1c28" />

</waiter-list>

</SyncPoint>

</resource-list>

</deadlock>


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)

Group: General Forum Members
Points: 677126 Visits: 45592
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
456789psw
456789psw
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3228 Visits: 852
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
Jeff Moden
SSC Guru
SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)SSC Guru (677K reputation)

Group: General Forum Members
Points: 677126 Visits: 45592
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
456789psw
456789psw
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3228 Visits: 852
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search