How to Process a SSAS MOLAP cube as fast as possible – Part 1

Recently, with some colleagues, I was working on a project with a serious challenge; there was this Analysis Server 2012 system with 40 physical cores, half a Terabyte of RAM and 10TB of SSD storage waiting to get pushed to its limits but it was installed via the famous ‘next,next finish’ setup approach and we had to tune the box from scratch. Also we had to pull the data from a database running on another box which means the data processing will be impacted by the network round-tripping overhead. 

With a few simple but effective tricks for tuning the basics and a methodology on how to check upon the effective workload processed by Analysis Server you will see there’s a lot to gain! If you take the time to optimize the basic throughput, your cubes will process faster and I’m sure, one day, your end-users will be thankful! This Part 1 is about tuning just the processing of a single partition.

Quantifying a baseline

So, where to start? Well to quantify the effective processing throughput, just looking at Windows Task Manager and check if the CPU’s run at 100% full load isn’t enough; the metric that works best for me is the ‘Rows read/sec’ counter that you can find in the Windows Performance monitor MSOLAP Processing object. 

Just for fun… looking back in history, the first SSAS 2000 cube I ever processed was capable of handling 75.000 Rows read/sec, but that was before partitioning was introduced; 8 years ago, on a 64 CPU Unisys ES7000 server with SQL- and SSAS 2005 running side by side I managed to process many partitions in parallel and effective process 5+ Million Rows reads/sec (== 85K Rows read/sec per core).

The year 2006: Processing 300+ billion rows of retail sales data with SSAS 2005. 

Establishing a baseline – Process a single Partition

Today, with SSAS 2012 your server should be able to process much more data; if you run SQL and SSAS side by side on a server or on your laptop you will be surprise on how fast you can process a single partition;  expect 250-450K Rows read/sec while maxing out a single CPU at 100%.

As an impression of processing a single partition on a server running SSAS 2012 and SQL 2012 side by side using the SQL Server Native Client:  the % processor time of the SSAS process (MSMDSRV.exe) is at 100% flatline. Does this mean we reached maximum processing capacity? Well… no!  There is an area where we will find a lot of quick wins;  lets try if we can move data from A (the SQL Server) to B (the Analysis Server) faster.

Sample baseline:  290K rows read/sec - 100% Processor time of the SSAS process (msmdsrv) , maxing out a single CPU.

100% CPU?

Max’ing out with a flatline on a 100% load == a single CPU may look like we are limited by a hardware bottleneck. But just to be sure lets profile for a minute where we really spend our CPU ticks. My favorite tool for a quick & dirty check is Kernrate (or Xperf if you prefer).

Command line:

Kernrate -s 60 -w -v 0 -i 80000 -z sqlncli11 -z msmdsrv -nv msmdsrv.exe -a -x -j c:\symbols;

Surprisingly more than half of our time isn’t spend in Analysis Server (or SQL server) at all, but in the SQL native Client data provider! Lets see what we can do to improve this.

Kernrate output of profiling 60 seconds of MOLAP cube processing. 

Quick Wins

1) Tune the Bios settings & Operating system

Quick wins come sometimes from something that you may overlook completely, like checking the BIOS settings of the server. There is a lot to gain there; expect 30% improvement -or more-  if you disable a couple of energy saving options. (its up to you to revert them and save the planet when testing is done…)

For example: 

- Enter the Bios Power options menu and see if you can disable settings like ‘Processor Power Idle state’. 

- In the Windows Control Panel, set the Server Power Plan to max. throughput (up to Windows 2008R2 this is like pressing the turbo switch but on Windows 2012 the effect is marginal but still worth it).

Control Panel- Power Options 

2) Testing multiple data providers

Like the kernrate profiling shows, a lot of time is spend in the network stack for reading the data from the source. This applies to both side by side (local) processing as well as when you pull the data in over the network.

Since the data provider has a significant impact on how fast SSAS can consume incoming data, lets check for a moment what other choices we have available; just double click on the cube Data Source

Configuring Connection String options.


Switching from the SQL Native Client to the Native OLE DB\ Microsoft OLE DB Provider for SQL Server brings the best result: 32% higher throughput!

Use the Native OLE DB Provider for best throughput. 

SSAS is still using a single CPU to process a single partition but the overall throughput is significant higher when using the OLE DB Provider for SQL Server:

OLE DB Provider for SQL Server 

To summarize; with just a couple of changes the overall throughput per core just doubled!Summary


Reading source data from a remote Server faster

if you run SSAS on a separate server and you have to pull all the data from a database running on another box, expect the base throughput to be significant less due to processing on the network stack and round tripping overhead. The tricks that apply to the side by side processing also apply in this scenario:

1) Process the Partition processing baseline against the remote server.

Less rows are processed when reading from a remote server (see fig.); also the MSMDSRV process is effective utilizing only 1/2 of a CPU. The impact of transporting the data from A to B over the network is significant and worth optimize. Lets focus our efforts on optimizing this first. 

Reading from a data source over the network.


2)  Increase the network Packet Size from 4096 bytes  to 32 Kbyte.

Get more work done with each network packet send over the wire by increasing the packet size from 4096 to 32767;  this property can be set via the Data Source – Connection String too; just select on the left ‘All’  and scroll down till you see the ‘Packet Size’ field.

Change the packet size in via the Cube Data Source Connection 'All' section.

The throughput gain is significant:

Quick win : increase network packet size from 4KB to 32KB. 



When you have a lot of data to process with your SQL Server Analysis Server cubes, every second you spend less in updating and processing may count for your end-users; by monitoring the throughput while processing a single partition from a Measure Group you can set the foundation for further optimizations. With the tips described above the effective processing capacity on a standard server more  than doubled. Every performance gain achieved in the basis will pay back later while processing multiple partitions in parallel and helps you to provide information faster! 

In part II we will zoom into optimizing the processing of multiple partitions in parallel.

GD Star Rating
GD Star Rating

Introduction to SQL Server 2012 Parallel Data Warehouse

Surprisingly how we over the years have accepted that we need to invest a lot of time and energy to move data from ‘A’ to ‘B’  and to maintain a well performing data warehouse platform where your reports & queries respond within a reasonable amount of time (and I’m not referring to just SQL Server :-) )  But ‘Good’ isn’t good enough anymore; by entering the world of Big Data and the Internet Of Things your users expect sub-second ‘search engine alike’ response times from their in-company data platform too, even if new data hasn’t been crawled before or when data has to be read from an Hadoop cluster…

Luckily we can shift gears easily and move from a reactive- into a predictive mode;  SQL Server 2012 Parallel Data Warehouse (aka PDW) is just that; the next generation data platform that performs at top speed from day one with many of the tuning optimizations as described on my blog already build in. With PDW in place you can focus on other things than Query performance tuning or waiting for data loads and exports to complete ! 

The secret sauce

The beauty of PDW is the simplicity of the scalability concept: just add more SQL Server Compute nodes and benefit from the seamless scale out engine: under the hood the PDW engine ‘stripes’  the data across many so called ‘data distributions’  (== 8 separate tables per compute node) a concept that makes it easy to keep up with your data growth needs and performance needs. 

Also, SQL Server 2012 PDW already offers the new native updateable clustered columnstore Index (CCI) storage format. I’ve been working with the CCI for over 8 months now and I must say; it works like a charm! High data selectivity and significant higher data compression ratios for faster highly Parallelized query response timings. (Expect runtimes of just minutes instead of hours, or seconds instead of minutes) The new CCI algorithms allow you to search through data faster; whether is doesn’t matter if the data already resides In-Memory or not.
Your business analysts and end users will generate more extensive results faster.

Schematic overview of the SQL PDW parallel scalability concept; simply add more Compute nodes to increase the data load and query speed + overall storage capacity.

Polybase for high-speed parallel Hadoop access

For many customers Hadoop is still just a topic on the ‘todo’ list but in case you want to get started you don’t have to be afraid of creating another data island in the lake; PDW  has build in support for either writing data out or querying data stored on an Hadoop filesystem via Polybase. Polybase is fast via direct parallel HDFS access to the data nodes. From within your SQL PDW databases simply use the ‘Create External table’  command and you can both either store- or query data to /from hadoop!

Below an example of querying an hadoop cluster folder that contains many separate files with Twitter feeds; access the twitter data with a regular query without even bothering where the data comes from and receive answers within sub-seconds, as if it was stored within PDW locally !

Query Twitter Data log files stored on hadoop from within SQL Server PDW. 

More info on PDW Benefits (Must Reads!)

If you want to read more on SQL Server PDW, please check out :

The PDW landing pages is simple to remember :

Or… download the whitepaper in Word format that I helped writing to explain PDW and the benefits that my customers value most when they start using SQL Server 2012 PDW,  Enjoy ! Server 2012 Parallel Data Warehouse – A Breakthrough Platform

GD Star Rating
GD Star Rating
Better Tag Cloud