XMLA Query Stops Processing Consistently after being run 5 times

Question

XMLA Query Stops Processing Consistently after being run 5 times

Padraic.Hickey 75286

Valued Member

Points: 56
More actions
March 14, 2013 at 10:25 am

#147739

Hello all,
Any help with this is appreciated. I have very little experience with SSAS so any info suggestions are good. Also If you need any more info let me know.
I will explain the the xmla Query, machine setup, then the cube setup and then the steps I have taken to try and solve the problem.
The XMLA Query:
I have an xmla query which when run consistently fails after about 5 runs.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel maxParallel="4">
<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">
<Object>
<DatabaseID>CUBENAME</DatabaseID>
<CubeID>CUBENAME</CubeID>
<MeasureGroupID>V Fact Answer</MeasureGroupID>
<PartitionID>FactAnswer_2001</PartitionID>
</Object>
<Type>ProcessIndexes</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>

<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">
<Object>
<DatabaseID>CUBENAME</DatabaseID>
<CubeID>CUBENAME</CubeID>
<MeasureGroupID>V Fact Calculated Benchmark Questionnaire Group Gaps</MeasureGroupID>
<PartitionID>V Fact Calculated Benchmark Questionnaire Group Gaps</PartitionID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parrallel>
</Batch>
The XMLA query takes about 10 seconds to run. Then inexplicably it stops running.
The Last 2 Messages I see are:
Finished processing the 'FactMetadataAnswer_2003' partition.
Finished processing the 'Metadata Answer' measure group.
The query does not timeout. Does not respond to cancel command. The only solution is to restart the machine. We have this problem in on several test environments at the moment.
Machine Setup
The machine is Windows 2008, 8.00GB RAM, 64 bit, Dual Processors (2.53 GHZ) each.
SQL Server is installed on the same machine.
Cube Details
In the cube we have 17 dimensions. 18 Measure Groups. Each measure Group is partitioned by year since 2001. So in total there are about 240 partitions across all the measure groups.
Steps to Solve the Problem
1. Because SQL Server is installed on the same machine I have restricted both SSAS and SQL Server to only use a maximum of 40% of the memory. Initially SQL Server was setup to use all available memory and SSAS around 80%.
2. The CommitTimeout was set to zero. Thinking that the commit was taking forever I set this value to 30000 (5 minutes). This had no affect.
3. ThreadPool \ Process \ MaxThreads - Increased from 64 to 150. No affect.
Next I started analysing the Performance Counters:
One strange thing I have noticed:
1. All values for MSAS 2008: Cache stay at zero. I don't know what this means. Anyone know if this is bad?
For these errors I have logged all the MSAS performance counters and I have the flight recorder switched on. I can provide lots more info if needed. I am really just looking for suggestions as for things to do, things to check, etc.
Cheers,
Padraic

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Mackers SSCrazy Points: 2935 More actions · Answer 1

Have you looked at the SQL it is running at the time?

I've seen something very similar when our weekly SQL server maintanence jobs failed for several weeks (I didn't have access to see this & the DBAs didn't notice). The tables were not getting their indexes rebuild & stats updated and so SQL Server was choosing a bad query plan

Try running this to get a the queries you need to run:

select distinct 'UPDATE STATISTICS ' + name

from sys.tables

union all

select distinct 'DBCC DBREINDEX (''dbo.' + name + ''', '''', 90)'

from sys.tables

Mack

Mackers SSCrazy Points: 2935 More actions · Answer 2

I have also seen something similar when we were using 32-bit SSAS and this stopped happening when we moved to 64-bit SSAS and the extra RAM that came with it

I'd say that 8Gb of Ram is a very modest amount especially if you are using SQL Server & SSAS (& the OS) on the same server

Have you got a spare SSAS server? Maybe try turning SSAS off on you problem server and try processing the cube on a spare server pointing to your problem server's SQL instance

Mack

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

What do you see happening when you run a trace on the ssas machine?

Steve.

Padraic.Hickey 75286 Valued Member Points: 56 More actions · Answer 4

So my flight recorder has only captured data for the last hour. I ran these tests about 2 hours ago. Give me a little bit of time and I will re-run and re-generate the trace file.

Mack I will first get the trace data and then I will look at the indexing and RAM.

Padraic.Hickey 75286 Valued Member Points: 56 More actions · Answer 5

Hey Steve,

So again it failed after the 5th run. I have checked the log files and I noticed something which may be the problem. At the same time the queries are being run I can see calls from the Report Server.

Each time the XMLA query runs it generates about 10 calls to the database. The query that failed had a query from the Report Server interrupt it before all 10 calls were complete. The first call from the report server was:

exec sp_reset_connection

So to explain it better:

Log file when Success:

Query Analysis Server Started