1

I have two xmla queries (to process SSAS Cubes) that have been embedded in two separate SQL Server scheduled jobs. These jobs get triggered by some code at random time and there may be a time when both of the jobs get triggered at the same time. In this case one job (actually XMLA query associated with that scheduled job) get precedence and other keeps waiting till the prior finished. I would like to execute both xmla queries simultaneously. Is that possible. The structure of both XMLA is as below:

  <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>SSAS Database ID here</DatabaseID>
        <CubeID>Cube ID here</CubeID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

Looking for the possible suggestions/answers.

Suresh
  • 156
  • 1
  • 11

1 Answers1

1

If both processing instructions access a common object (partition or dimension), then one must wait for the other to finish. Analysis Services - being optimized for OLAP - uses a very coarse granular locking model: Each write access to an object like a partition or a dimension places an write lock on this object which is only released after the job finishes - be that successful or with an error.

In case both processing jobs only access different objects, there would be no conflict, and they could run in parallel (e. g. job 1 processing dimensions A and B and measure group X, and job 2 processing dimension C and D and measure groups Y and Z).

This coarse granularity of locking is one of the aspects on being fast, as there is no need to check for locks on each data row you access - as is the case for most relational databases , which causes some overhead.

FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • Thanks Frank - Just to add more to my question - Both the xmla queries process the different dimensions and different cubes, only the ssas database is same. – Suresh Jan 24 '14 at 06:46
  • Just noticed that this behavior is specific to SSAS 2005, I have tested it in 2008R2 and it is working without waiting for the first. Is there any SQL Server settings that control such behavior? – Suresh Jan 24 '14 at 09:41
  • @Suresh I have not extensively used AS 2005. But maybe it is a bug fixed in 2008R2. – FrankPl Jan 24 '14 at 12:07