Encountering deadlocks with Proactive Caching
I have a couple of cubes that I'm wanting to designate for real-time ROLAP storage, so I've set up the cubes, and each of their dimensions, to use Proactive Caching. The cubes are defined to use a Scheduled Polling query notification scheme, and the dimensions are set to use SQL Server notification.
According toperfmon (the "Total Deadlocks Detected" counter), this setup is somehow resulting in numerous deadlocks. I tried disabling proactive caching for all but two dimensions, but still encountered this problem. Has anyone else encountered this, or have any information as to why this configuration is resulting in deadlocks?
The server is running SQL Server 2005, SP2. My workstation (on which I can reproduce this issue) also has a dual core processor.
Thanks,
Joe Miller
Have you tried running SQL Profiler against the SQL server in order to diagnose the deadlocks? I believe profiler should be able to show you the two statements that are causing the deadlocks. If it is you proactive caching queries locking up with the standard ROLAP queries, then looking at your indexing setup or adding hints to your polling query may help.
One possible solution would be to use the snapshot isolation mode, but you would really want to understand what is causing the deadlocks and consider the other side effects of snapshot isolation before going down this path.
Thanks for the response, Darren. I appreciate any help you can offer.
I had run Profiler against the OLAP database, which helped me identify the objects affected by the deadlocks (e.g., the designated victim), but the deadlock graph doesn't provide any information about the queries that caused them. I also tried qualifying the queries with nolock hints, but to no avail.
The deadlocks occur even in the following scenario, which I particularly find odd:
- Set up proactive caching for a dimension (or a partition), for real-time ROLAP.
- Select scheduled polling for the notification type, using "select getdate()" as the polling query.
(My goal with the above polling query is basically to ensure the cache is cleared out with each polling. We would like to disable SQL Server's caching mechanism completely for these particular cubes, but that doesn't appear to be an option...)
I'm not really familiar with the new snapshot isolation level, but from the description I read on Books Online I'm not sure it would help in this situation. It seems like the deadlocking is occuring on the OLAP side of things, rather than on the relational side. (For example: when the deadlocking starts up en masse, the OLAP database becomes unresponsive - or *very* slow - but the relational DB answers queries just fine.)
Thanks again for your help,
Joe Miller
The OLAP side of things (SSAS) does not have the concept of deadlocking, so it is impossible that the deadlocks are coming from SSAS, they must be coming from the relational system. When you are using ROLAP storage the OLAP engine is pretty much only a semantic layer (with a bit of caching) it translates MDX queries into SQL queries and executes them against the relational source and then massages the results into a multi dimensional rowset.
I am guessing that the OLAP queries are unresponsive as they are probably being locked up by the deadlocks and as the queries are only reading it is most likely always being chosen as the deadlock victim and having to re-try.
I'm assuming that your relational source is SQL Server 2005 because of your reference to deadlock graphs? If you export out the deadlock graph to a file you should be able to open it up and see atleast part of the statements that triggered the deadlock (see http://www.extremeexperts.com/SQL/Yukon/DeadLockDetection.aspx)
Because you are after real time ROLAP I am assuming that the issue is with the fact that you have data that is being constantly updated and constantly queried. The locking semantics in SQL Server mean that readers can block writers which means if your readers are doing joins against multiple tables and your writers are updating mulitple tables in one transaction. Snapshot Isolation might help because it will stop "readers" and "writers" blocking each other, effectively readers get to see a snapshot version of the row as it was when the query started. But we really need to determine if this is actually the case before going down that path. It could also be that a slight change in the way your updates are applied could resolve the issue too.
Thanks again for the response, Darren.
I'm a little confused as to what it is I'm seeing if SSAS doesn't have any concept of deadlocking. The deadlock graphs that I am getting are being retrieved from Profiler running against SSAS. The instance of Profiler that I have running against the relational database (simultaneously) actually doesn't log any deadlock information at all, in these situations.
And you are correct with your assumption about our relational DB (SQL Server 2005 SP2). The deadlock graph retrieved from the SSAS Profiler, though, doesn't contain nearly as much useful information as the ones retrieved from a relational DB (e.g., like the one shown in the link you provided). The XML just consists of a list of Lock elements, each of which represents a lock involved in the deadlock, including information like Lock ID, Lock Status, Lock Type, etc.
From the data I'm getting from these traces, it really looks like SSAS is (in a sense) tripping over itself when rebuilding the cache(s). If you're certain that the deadlocking isn't occurring on the SSAS side, I'll believe you - but it makes this issue even more bizarre. 
Thanks,
Joe Miller
I think that what you are seeing in the SSAS profiler is the error from SQL Server "bubbling" up. I have not had to do any realtime ROLAP in a production environment, so this is all just theory on my behalf, but I am pretty sure that this is what is happening.
When you trace the Relational source are you filtering on a particular Database? There is a bug in the deadlock event where the database Id column is not populated, thus if your trace is filtered to a particular database you will not see any deadlock events. What people are having to do as a work around is to run a separate trace just catching all deadlock events. Try this and see if you have any luck.
That would make sense (the SSAS Profiler instance catching a "bubbled-up" version of a deadlock error), but since I'm not seeing anything on the relational Profiler I'm not sure that this is what's happening. Unfortunately, I'm not running any filters on the Profiler instances, so the bug you described isn't affecting our results here.
I'll continue re-running these traces to see if anything else crops up... but so far I'm still not seeing anything problematic on the relational DB side.
Thanks again for your help,
Joe Miller
Unfortunately there does not appear to be a lot of people using ROLAP storage so I don't know if you will be able to get much more help of this forum. Maybe there could be some locking at the SSAS layer, but I was pretty certain that the ROLAP storage delegated all of that to the underlying relational database.
This is sounding like it might be something where you might need to put in a call with Product Support. I would be interested to hear back when/if (hopefully when) you find a resolution.
Well, I appreciate your help in any case, Darren. I'll be sure to post back here once I know more.
Thanks,
Joe Miller
