Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

XMLA Query Stops Processing Consistently after being run 5 times Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 10:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 2:39 AM
Points: 6, Visits: 15
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>

<!-- .......
There are about 180 ProcessesIndexes followed by about 5 ProcessFulls.
-->

<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
Post #1431100
Posted Thursday, March 14, 2013 10:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
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
Post #1431106
Posted Thursday, March 14, 2013 10:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
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
Post #1431119
Posted Thursday, March 14, 2013 10:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Sunday, April 13, 2014 11:43 PM
Points: 1,781, Visits: 3,340
What do you see happening when you run a trace on the ssas machine?


Steve.
Post #1431126
Posted Thursday, March 14, 2013 11:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 2:39 AM
Points: 6, Visits: 15
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.
Post #1431134
Posted Thursday, March 14, 2013 11:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 2:39 AM
Points: 6, Visits: 15
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
Query Analysis Server Completed
Query Analysis Server Started
Query Analysis Server Completed
Query Analysis Server Started
Query Analysis Server Completed
Query Analysis Server Started
Query Analysis Server Completed

Log File when Failure:
Query Analysis Server Started
Query Analysis Server Completed
Query Analysis Server Started
Query Analysis Server Completed
Query Analysis Server Started
Query Analysis Server Completed
Query Analysis Server Started
Report Server : exec sp_reset_connection (Does this reset all connections?)
Report Server
Report Server
Query Analysis Server Completed

I am going to try this again with the Report Server turned off.


Post #1431161
Posted Thursday, March 14, 2013 12:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 2:39 AM
Points: 6, Visits: 15
So I repeated the process. This time with the Report Server turned off. This made no difference.
This time it failed after 4 runs.

The 5 queries are running in under a second. They are all completing very quickly. There is no locking or deadlocking taking places. They are not returning that much information.

Mack its definitely not an issue with indexes. I am also not inclined to believe its an issue with RAM. This would should up in the performance monitor. Also why would it stop after a couple of runs.

Any other suggestions?
Post #1431177
Posted Friday, March 15, 2013 2:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
When we had issues with 32-bit SSAS it did a similar thing - it just seemed to flatline and hence it took so long to find the issue

I noticed that you are parallel processing the cube, have you tried serially processing the cube or processing the dimensions, then the data then the indexes?

Does it hang on the same partition group or the same partition?

Mack
Post #1431395
Posted Friday, March 15, 2013 4:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 2:39 AM
Points: 6, Visits: 15
Hey Mack,

Thanks for all the great suggestions. So turning off the parallel processing appears to have done the trick. I have been able to run the query 10 times without fail.

I was going to re-order the processing but it is working now so I won't touch it.

Not sure what long term effect this will have but this should buy me more time to develop a much better understanding of what exactly SSAS is.

Thanks again,
Padraic
Post #1431440
Posted Friday, March 15, 2013 5:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
If it works serially then maybe it's a problem with parallelisation as SSAS is sending multiple queries to SQL Server on a 2 CPU box

I've had issues with DTS where I have had CPU locking (never had this with SSAS as I've always had them on seperate boxes). You could look to try adding MAXDOP to your SQL queries

http://blog.sqlauthority.com/2010/03/15/sql-server-maxdop-settings-to-limit-query-to-run-on-specific-cpu/

You may need to reduce the number of threads SSAS opens when processing (I believe you increased this and it may make the issue worse)

http://technet.microsoft.com/en-gb/library/cc966527.aspx

Serially processing may well be fine if it doesn't add too much time to your overall processing but it'd be good to understand what the issue is

Less helpfully (unless you work for HP and you have servers coming out of your ears) Microsoft do recommend that SSAS & SQL Server are on different boxes. Splitting them up may help

Mack

Post #1431454
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse