I have an Analysis Services database that stalls unexpectedly at the end of processing. I am running a XMLA command from SSIS that performs a full process on all dimensions, and then all cubes. It looks something like this (altered for readibility and confidentiality):
.... REST OF DIMENSIONS ....
.... REST OF CUBES ....
I have performed a trace using Profiler, and see something very curious that doesn't occur in my Staging/Test environment. In the trace, Analysis Services successfully processes new dimension, partition, measure group, and cube objects as part of a single transaction. During this time users are actively querying the database, and getting acceptable performance. After it completes processing all objects, it obtains a lock on the database and begins updating the objects in the file system, at which point I see Notification - Object Altered events in Profiler. After it updates the last object, which is the database object, the database become unresponsive for a period of around 30 minutes. During this time, there doesn't appear to be any activity on the server (processors, IO, and memory all appear to be idle), there are no new trace events registered in profiler, and users are unable to connect and execute queries. The last profiler trace event that I see prior to this stall is the object altered event for the database object.
After approx 30 minutes, profiler suddenly reports that my XMLA processing command has completed (finally), and begins answering user requests again. At this point things go back to normal.
Has anyone ever seen this behavior before, or know what may be causing this stall?
Thanks in advance for any insight you can provide,
That's a really difficult problem to troubleshoot, but here are some thoughts.
First check the ForceCommitTimeout setting. This article describes that more:
Listening to your problem, I would guess that the sequence of events may be the following:
1. Cube processing starts
2. Longrunning MDX Query A starts
3. Cube processing finishes and looks for an exclusive lock to commit changes
4. Cube processing can’t get an exclusive lock, so it gets a pending commit lock (which block all future connections)
5. Cube processing waits 30 seconds (or whatever ForceCommitTimeout is set to) then tries to cancel MDX Query A
6. If MDX Query A isn’t responsive to cancel, everything just sits and waits until it finally cancels
I would also install the latest cumulative update (CU7 is the newest at the moment) as I believe that has some fixes that will make MDX queries more responsive to cancelling (as compared with SP2 itself).
If it keeps happening and you're desperate, you may want to restart SSAS right before your cube processing to forcefully close any other connections/sessions using a bat file like this:
net stop MSSQLServerOLAPService
net start MSSQLServerOLAPService
If you have future lock ups, I would also fire up the ActivityViewer sample app and see if you can see any other connections, sessions, or locks. (Though usually if the scenario is as described above, ActivityViewer will be locked out, too... which sort of defeats the purpose of that sample app :-)
I think I are facing with similar issue. Can you please clarify two things.
1. How can i verify that this is what is happening. Can i check if MDX Query cancel has been initiated ? In flight recorder may be ?
2. If I change 'Full Process' to 'Process Update' will it resolve the issue ?
Also interestingly, last entry in my ssis logs says 'Finished processing the 'SSAS' database' on 'OnProgress' event. Is it expected ?