Getting Deadlock Error During Processing


Intermittently Getting below mentioned error during processing
OLE DB error: OLE DB or ODBC error. Transaction (Process ID n) was deadlocked on thread| communication buffer resources with another process and has been chosen as the deadlock victim.

A deadlock is a circular blocking chain, where two or more spids are each blocked by the other so that no one can proceed.

You don’t have control over the Select Statement generated by SSAS Processing job (If you are not using Named Query in DSV), in that case you need take care of things from SQL Engine Perspective.

Here are few recommendations, which can reduce deadlock situation:

  • Use separate environment for Reporting and Transaction
  • Ensure the database design is properly normalized.
  • Have the application access database objects in the same order every time.
  • Avoid cursors in DW environment
  • Use as low a level of isolation as possible for user connections.
  • Right indexes for getting correct execution plan (rebuild indexes after huge data modifications)
  • Have updated Statistics what will generate a right execution plan
  • During DW data refresh don’t do Cube Processing
  • In DSV refer View instead of Tables - Processing through a view provides you with an extra layer of abstraction on top of the database  is a good design strategy. In the view definition, you can add a NOLOCK or TABLOCK hint to remove database locking overhead during processing.
  • Try experiment with MAXDOP setting - Set it to one, in your dev / test environment and see if you can reproduce issue; Keep in mind Performance Caveat of using MAXDOP=1.



How To Monitor Deadlocks in SQL Server

Comments (2)

  1. Gustavo says:

    Hi Karan.

    I'm using SQL Server 2012 SP2 and AS also 2012 SP2

    As you work in the AS team, do you know how to make the AS automatic generated query to append some hint in the end ?

    I have a scenario where I could benefit from appending option ( HASH join) to force join type (SQL QO is incorreclty using loop join in some cases with lots of referenced dimension) at the end of the query ?

    I can't use plan guides as the automalically generated query is bound to change in the future.

    Also I'm having this problem of intra query parallelism deadlocks from time to time.

  2. Rambabu says:

    Hi All,

    when i  execute the job for cube refreshment,sometimes it is executing successfully,but sometimes it is getting failed with below error  

    Started:  4:08:08 AM  Error: 2015-01-14 04:55:21.03     Code: 0xC11D0005     Source: Analysis Services Processing Task Analysis Services Execute DDL Task     Description: Transaction errors: The lock operation ended unsuccessfully because of deadlock.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:08:08 AM  Finished: 4:55:21 AM  Elapsed:  2832.18 seconds.  The package execution failed.  The step failed.

    If any find the solution please post on it.



Skip to main content