Deadlock Troubleshooting in SQL Server Analysis Services (SSAS)
A deadlock is a special type of situation wherein two or more transactions are waiting for the other to finish, and thus neither ever does and no one can proceed. Deadlocks can be often considered as normal in a high concurrent update environment. When a deadlock is detected, some systems (such as SQL Server) will choose the least expensive process to rollback to resolve the deadlock.
Deadlocks not only happen in OLTP systems but can also happen in an OLAP environment. If you suspect you are having some deadlock issues with SQL Analysis Services, you can follow the example below to trouble shoot the problem.
The example in the blog here is taken out from a SSAS 2005 server, but the same concept and steps should be applicable in SSAS 2008 as well.
The best way to trouble shoot a deadlock in SSAS is to capture the whole scenario within a SQL profiler trace and then analyze the trace offline.
By default, the “Deadlock” event is not selected. You will need to explicitly select the Deadlock event under Locks within the Trace Properties (see screen shot #1 below)
Tags: management