CODEWITHSUNDEEP
sql-serverparallel-processingsql-server-2012ssascube
Metanormal
Metanormal

Reputation: 27

Cube Processing fails only in parallel mode

i do have a somewhat reproducable error, which i am no longer willing to bear, so i hope some of you might know a better workaround.

I have several larger cubes (around 10-50 GByte) which i process daily. The processing of only the partitions (about 20%) takes about 1h when i use in XMLA-Script - so processing dimensions and measures in paralellel-mode. This works in only 2/5 runs.

So i have a procedure which detects the crash and if happened starts the serial-processing instead, which will run about 2-5 times slower - but at least works every time.

The error codes are very generic and not very helpful:

And since it works every time in serial mode i know that there are no errors in principle.

I am using Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Enterprise Edition: Windows NT 6.3 (Build 9600:)

Please, if you have any ideas how to solve (workaround) this.

I am thankful for every new insight or idea you might have to this behaviour.

Upvotes: 0

Views: 1083

Answers (1)

Alex Peshik
Alex Peshik

Reputation: 1515

It looks like concurrency issue and/or heavy SQL server workload (e.g. caused by mostly unlimited threads from SSAS server). I guess, it's better to set up MAX-values for necessary parameters:

ThreadPool\Process\MaxThreads = 4*cores

DB Number of Connections = 2*cores-1 (it's based on my own practice). You can tune this right before and after processing task if it's necessary to have huge amount of connections not in process time.

And maybe somehow play with affinity masks, but previous parameters tuning should be enough.

This article http://phoebix.com/2014/07/01/what-you-need-to-know-about-ssas-processor-affinity/ and this book http://msdn.microsoft.com/en-us/library/hh226085.aspx describe whole technique in details.

UPDATE:

There is also small possibility of wrong TCP settings, described here: http://blogs.msdn.com/b/cindygross/archive/2009/10/22/sql-server-and-tcp-chimney.aspx

But this may cause fails even in serial processing.

Upvotes: 3

Related Questions