Hi,

Below is the deadlock which occurs when running a SELECT statement. I was puzzled by this dead locks as there is only a single process (spid 79) involved in the deadlock. 
Somehow the SQL Server doesn't add any Deadlock graph event Profiler trace. The server is running SQL Server 2008 R2 with CU13. 

deadlock-list
 deadlock victim=process1675c508
  process-list
   process id=process1675c508 taskpriority=0 logused=10000 waittime=548 schedulerid=38 kpid=27844 status=suspended spid=79 sbid=0 ecid=1 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process16791dc8 taskpriority=0 logused=10000 waittime=548 schedulerid=40 kpid=38704 status=suspended spid=79 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process1675c748 taskpriority=0 logused=10000 waittime=512 schedulerid=38 kpid=44828 status=suspended spid=79 sbid=0 ecid=8 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process169c54c8 taskpriority=0 logused=10000 waittime=509 schedulerid=42 kpid=27836 status=suspended spid=79 sbid=0 ecid=12 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process1675cbc8 taskpriority=0 logused=10000 waittime=510 schedulerid=38 kpid=65656 status=suspended spid=79 sbid=0 ecid=10 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process16791048 taskpriority=0 logused=10000 waittime=510 schedulerid=40 kpid=64604 status=suspended spid=79 sbid=0 ecid=11 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process167aa988 taskpriority=0 logused=10000 waittime=548 schedulerid=41 kpid=14540 status=suspended spid=79 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process16791b88 taskpriority=0 logused=10000 waittime=512 schedulerid=40 kpid=66488 status=suspended spid=79 sbid=0 ecid=7 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process167aae08 taskpriority=0 logused=10000 waittime=510 schedulerid=41 kpid=48376 status=suspended spid=79 sbid=0 ecid=9 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process169c5288 taskpriority=0 logused=10000 waittime=548 schedulerid=42 kpid=58316 status=suspended spid=79 sbid=0 ecid=2 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process167abdc8 taskpriority=0 logused=10000 waittime=512 schedulerid=41 kpid=24712 status=suspended spid=79 sbid=0 ecid=6 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process169c5948 taskpriority=0 logused=10000 waittime=512 schedulerid=42 kpid=25404 status=suspended spid=79 sbid=0 ecid=5 priority=0 trancount=0 lastbatchstarted=2015-06-06T18:43:58.563 lastbatchcompleted=2015-06-06T18:43:57.640 clientapp=Microsoft SQL Server Analysis Services hostname=COLNJWN64AIO1 hostpid=1636 isolationlevel=read committed (2) xactid=1276779316 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=unknown line=3 stmtstart=12 sqlhandle=0x02000000fd55f61eb6f85919a8b250a4e371ca88466f0459
unknown     
    inputbuf
   process id=process10e4692088 waittime=510 schedulerid=38 kpid=0
  resource-list
   exchangeEvent id=Port80c62700 WaitType=e_waitPortOpen nodeId=14
    owner-list
     owner id=process16791dc8
    waiter-list
     waiter id=process1675c508
   exchangeEvent id=Port80c62700 WaitType=e_waitPortOpen nodeId=14
    owner-list
     owner id=process167aa988
    waiter-list
     waiter id=process16791dc8
   exchangeEvent id=Port80c62b00 WaitType=e_waitPortOpen nodeId=50
    owner-list
     owner id=process169c54c8
    waiter-list
     waiter id=process1675c748
   exchangeEvent id=Port80c63000 WaitType=e_waitPortOpen nodeId=52
    owner-list
     owner id=process1675cbc8
    waiter-list
     waiter id=process169c54c8
   exchangeEvent id=Port80c63000 WaitType=e_waitPortOpen nodeId=52
    owner-list
     owner id=process16791048
    waiter-list
     waiter id=process1675cbc8
   exchangeEvent id=Port80c63000 WaitType=e_waitPortOpen nodeId=52
    owner-list
     owner id=process167aae08
    waiter-list
     waiter id=process16791048
   exchangeEvent id=Port80c62700 WaitType=e_waitPortOpen nodeId=14
    owner-list
     owner id=process169c5288
    waiter-list
     waiter id=process167aa988
   exchangeEvent id=Port80c62b00 WaitType=e_waitPortOpen nodeId=50
    owner-list
     owner id=process1675c748
    waiter-list
     waiter id=process16791b88
   exchangeEvent id=Port80c63000 WaitType=e_waitPortOpen nodeId=52
    owner-list
     owner id=process10e4692088
    waiter-list
     waiter id=process167aae08
   exchangeEvent id=Port80c62700 WaitType=e_waitPortOpen nodeId=14
    owner-list
     owner id=process169c5948
    waiter-list
     waiter id=process169c5288
   exchangeEvent id=Port80c62b00 WaitType=e_waitPortOpen nodeId=50
    owner-list
     owner id=process16791b88
    waiter-list
     waiter id=process167abdc8
   exchangeEvent id=Port80c62b00 WaitType=e_waitPortOpen nodeId=50
    owner-list
     owner id=process167abdc8
    waiter-list
     waiter id=process169c5948
   threadpool id=scheduler16760080
    owner-list
     owner id=process1675cbc8
     owner id=process1675c748
     owner id=process1675c508
    waiter-list
     waiter id=process10e4692088
Error: 1205, Severity: 13, State: 80.
Transaction (Process ID 79) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transac

Suman,

This is basically an intra-query-parallel query deadlock.

Read more about it here - http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx

Your options are to try and see if adding indexes are going to change that plan to be serial rather than parallel. You can also try to add maxdop 1 for the query and see if it works.

I have had these types of deadlocks resolved by both these methods.

I agree with Ashwin.

For further assistance, if you can post the SQL statement involved maybe we can suggest a rewrite or other relevant questions.  Also: how many cores on your server, what is your maxdop setting, and how long did this query run?  Guessing, 12 cores, maxdop=0, and just a second or so.

It's basically a SQL Server bug, but it's a generic thing that we've all seen occur, just have to work around it.

Josh

hi Josh,

Setting MAXDOP to 1 never completes the query.  The server is 4 processor/ 64 cores. So, want to leverage the process power offered by the hardware.

Raising a MS case would be of any help? 

>Raising a MS case would be of any help? 

Perhaps, but first apply the latest service pack and CU to see if the issue is resolved.  You appear to still be on SQL 2008 R2 SP2, which is almost out of support.

David

Did you try fine tuning the query so that it doesnt need a parallel plan? There r not much options with this !!

Setting MAXDOP to 1 never completes the query.  The server is 4 processor/ 64 cores. So, want to leverage the process power offered by the hardware.

Raising a MS case would be of any help? 

It's a long-standing problem in SQL Server, so all they are going to tell you is the same as here on the group, first apply newer service packs or versions, and second try maxdop=1.

How long is "never"?  With just one core it might take 64 times longer to finish - or worse!  I've seen worse, which is why I avoid maxdop=1.  But then what can you do, especially since you've paid for all those cores and a big fat license fee too?

Try SQL 2014, I guess.

Can you duplicate the problem in your dev environment?

(probably not)

Can you duplicate the problem in your production environment, if it's just a select?  If so, you could at least experiment with query hints and additional indexes.  If it just happens randomly, well, that's harder to address.

Josh

ps - is your server with 64 cores running default maxdop=0?  It probably should not.  Changing the default for the server is an important step, and then you can also try setting down the maxdop number to different, smaller values just for this query.  Generally setting it down to 8 or so does not slow things down, very few queries really use a full 64 cores for more than a brief step or two in a larger plan.

• Edited by JRStern 7 hours 3 minutes ago
• Proposed as answer by Naomi N 5 hours 55 minutes ago