Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We're using SQL Server Analysis Server 2012 (Enterprise) and have a process to update the cube during the day. This process is running pretty well until it gets to the final part of processing where it swaps the old cube out and puts the new cube in place.

If we run a trace on SQL Profiler monitoring the locks we see this happen:

<LockList>
    <Lock>
        <Type>Commit Write</Type>
        <LockStatus>Acquired</LockStatus>
        <Object>
            <ServerID>MSOLAP$LIVE</ServerID>
        </Object>
        <ObjectID>51B3ABD7-5FAA-4D1E-89B4-F35E83042762</ObjectID>
    </Lock>
    <Lock>
        <Type>Commit Write</Type>
        <LockStatus>Acquired</LockStatus>
        <Object>
            <DatabaseID>DatabaseID</DatabaseID>
        </Object>
        <ObjectID>370037F0-FEA6-42FF-B6E6-2DFE8882E364</ObjectID>
    </Lock>
</LockList>

25 seconds later, we see:

<LockList>
    <Lock>
        <Type>Write</Type>
        <LockStatus>Released</LockStatus>
        <Object>
            <DatabaseID>DatabaseID</DatabaseID>
            <DimensionID>DimensionID1</DimensionID>
        </Object>
        <ObjectID>87B8A2FD-EC63-4214-A490-A8A07EC6DD15</ObjectID>
        <Duration>603062</Duration>
    </Lock>
    .../*SNIP: all of our dimension names */...
    <Lock>
        <Type>Commit Write</Type>
        <LockStatus>Released</LockStatus>
        <Object>
            <ServerID>MSOLAP$LIVE</ServerID>
        </Object>
        <ObjectID>51B3ABD7-5FAA-4D1E-89B4-F35E83042762</ObjectID>
        <Duration>25846</Duration>
    </Lock>
    <Lock>
        <Type>Commit Write</Type>
        <LockStatus>Released</LockStatus>
        <Object>
            <DatabaseID>DatabaseID</DatabaseID>
        </Object>
        <ObjectID>370037F0-FEA6-42FF-B6E6-2DFE8882E364</ObjectID>
        <Duration>25846</Duration>
    </Lock>
</LockList>

During this time we're seeing very little CPU or IO usage through process monitor. Also while this lock is in place, all of our users who are trying to query the cube are blocked.

We have only noticed this problem since moving to SQL 2012 Enterprise (we came from SQL 2005 Standard about 3 weeks ago), although it could have been pre-existing. No other changes have been made to the process.

From what I understand from sites like these 2 (http://gvkamath.wordpress.com/2011/11/02/committimeout-and-forcecommittimeout-in-ssas/), when SSAS receives the XMLA to process, it creates a new cube without blocking the existing one, and then as the last step it "swaps" the new cube over the old cube.

Currently, it's the only process that is doing updates on the cube and is running in Transaction mode so there should be no locking on the master.vmp file.

We have tried shrinking the FORCECOMMITTIMEOUT setting down to 5 seconds. Most of our read queries though are sub 1 second so they are not killed anyway. The problem we have is the queue of these short queries that line up behind the 23530 second lock that the process task has.

What else can we try or investigate to reduce the time of the Commit Write locks on the server?

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.