Vizplan, Network Data Transfer & Max Memory

[patrickbachmann]

Hi folks,

I've been having loads of fun troubleshooting a particularly troublesome query that's getting memory allocation errors.  I've had to do lots of deconstruction and testing tidbits of the SQL to avoid the memory allocation error so that I could at least run a visual plan.  When watching memory utilization on the multi-node server I'm not seeing any big spikes.  When I run the various chunks of code separately i'm not seeing any huge smoking gun or large volume of records.  In fact about the only thing I'm noticing is the majority of time is lumped under 'Network Data Transfer'. 

My questions;

1) Could the network data transfer be related to the multiple nodes communicating with each other?  Any more information about troubleshooting or analyzing this would be helpful to me.

2) There is a Max Memory Usage metric throughout my plan but it always shows 0 Byte.  So I'm wondering if this feature does not work.  Does anybody get values visible here?  Seems like it would be a huge benefit to see this especially troubleshooting memory allocation errors.

This particular environment we are on SPS8 rev 85.

Thanks for any helpful info.

-Patrick

[patrickbachmann · Accepted solution]

Also I can't seem to generate this 'Network Data Transfer' process box to appear in my single node system when I try a bunch of different queries.  So I'm not sure if this is because it's single node or it's because there is a different in vizplan for rev 82 (our single node HANA) vs rev 85 (our multi node BW HANA).

-Patrick

[patrickbachmann · Accepted solution]

Ok I have confirmed the network data transfer only appears on the multi node system (rev 85).  The same query on single node system (rev 85) is not showing any network data transfer.  In my case I'm seeing 60%+ of the cost timewise is network data transfer.

[patrickbachmann · Accepted solution]

Lars must be hibernating for the winter or taking 3 month European vacation. 

[lbreddemann · Accepted solution]

Ha!

As I am located in Melbourne now, the times of nice 30 days/year paid vacation are gone for good.

Looks like there is a downside to everything.

Your assumptions are correct.

The network traffic in PlanViz gives you the inter-node data transfer during query execution.

Things like large joins of tables that are located on different nodes require data transfer between those nodes. And this this takes more time to perform the same operation in main memory on a single node, you will see longer execution times for such queries on multi-node systems.

This is one of the reasons for why on multi-node systems the data distribution is pretty important to get right. SAP BW on HANA does this mostly automatic (or is supposed to do it automatically - there are a few SAP notes with corrections for that out. So make sure these SAP notes are installed).

For the max memory metric, the SAP HANA revision you're using might be too old.

In any case, SPS 8 is now fairly ancient. SPS 11 is out and SPS 10 got it's maintenance revision. Time to upgrade!

[patrickbachmann · Accepted solution]

G'day mate!  I didn't realize you were down under now.  Thanks for the feedback as always.  Very interesting indeed.  It's always good to have you to verify my many assumptions!  We actually have an 11 install now that I can soon use to test this memory feature too. 

Now remember when you're done surfing for the day be sure to go enjoy a veggiemite sandwich.

-Patrick