Archive
SSAS – Out of memory exception during processing
For a while now I been analysing a situation and i have got it down to a fine art now where by I do the following
- Create a memory a memory intensive situation where i am using 85% of available memory and SSAS is working hard to free memory.
- Process add a dimension and capture statistics.
- Review statistics, contemplate the results and then tweak something.
- Start at step 1 again.
The other day when i did my process add I was presented with the following error.
The following exception occurred while an operation was being performed on a data source view: The type initializer for ‘System.Xml.Schema.XmlSchemaComplexType’ threw an exception.;Exception of type ‘System.OutOfMemoryException’
The anatomy of a process update – Part 2
In part 1 of this 2 part post I shared an overview of the procedure to optimise process update. I will now walk through the optimisation process on one of our multi terabyte systems.
Part 1 shows a visualisation of process update and i have captured timings in profiler for all the stages shown and I now present my starting point for tuning below.
We have 3 stages that can be optimised.
- Dimension build – This comes in at 92 seconds which is acceptable so i won’t be discussing this further but you can refer to part 1 for tuning options.
- Partition index processing – Almost 25 minutes is spent here so tuning is required.
- Commits – Almost 7 agonising minutes are spent waiting for the phase 2 commit with the server lock in place.
The Anatomy of a process update – Part 1
I have been working on optimising the process update of one of our multi terabyte systems and this 2 part post will break down the optimisation process and share with you some very interesting results.
In part 1 I will share an overview of the procedure to optimise process update and in part 2 I will apply this to our system and share some startling discoveries.
Overview
Internally process update is a complex operation and can be difficult to describe so to facilitate explanation I have created the visualisation shown below of the process which also helps to show the single threaded and multithreaded execution paths.
Andrew Calvett's SQL Server Blog 