SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XMLA Query Stops Processing Consistently after being run 5 times


XMLA Query Stops Processing Consistently after being run 5 times

Author
Message
Padraic.Hickey 75286
Padraic.Hickey 75286
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
Mackers
Mackers
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 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
Mackers
Mackers
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 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
stevefromOZ
stevefromOZ
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: Moderators
Points: 3426 Visits: 3757
What do you see happening when you run a trace on the ssas machine?

Steve.
Padraic.Hickey 75286
Padraic.Hickey 75286
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
Padraic.Hickey 75286
Padraic.Hickey 75286
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
Padraic.Hickey 75286
Padraic.Hickey 75286
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 15
So I repeated the process. This time with the Report Server turned off. This made no difference. Hehe
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?
Mackers
Mackers
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 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
Padraic.Hickey 75286
Padraic.Hickey 75286
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
Mackers
Mackers
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search