HANA SPS 7 first dazzling highlights

December 3, 2013 | 126 Views |

Lars Breddemann

SAP HANA

csa Database hana hde In-Memory Computing Performance sap hana

Another half year passed and another SAP HANA SP has just been released.

SAP HANA 1.0 SPS 7 is out in the wild as revision 70.00 (double zero revision, yes Moneypenny 😉 ) and a lot of things had been changed, added and (hopefully) improved.

(linked from here)

Don’t get me wrong, this is by no means a comprehensive “What’s new” report (for that, make sure to check the SPS 7 documentation as soon as it is out as well https://help.sap.com/hana_platform).
While the documentation is not yet completely available on help.sap.com make sure to check the built in online help in SAP HANA studio (reference guides).

Also, check out SAP note 1944771 for release information.

ATTENTION: one of the major changes is that from now on, there exist maintenance revisions that are developed besides the SP revisions.

The maintenance revisions shall only contain bug fixes and keep functionality stable.

As a consequence of that, the upgrade paths between maintenance and SP revisions must be obeyed.

Read SAP note 1948334: SAP HANA Database Update Paths for Maintenance Revisions for the details.

Finally, don’t miss out on the WEBCAST series “SAP HANA SPS7 – Overview, Features, Details“

So this is what Ravi asked for – just quick glimpses

PlanViz changes

GUI improved

There’s even the option to execute a query and displaying the result set.

aborted plans still visible

That’s right, even though the query aborts with an error, you can review the PlanViz trace up until the POP that failed and analyze what was going on.

inverted index usage information (visible in the hover over details of POPs)

Thanks to my co-author, program lead and fellow Wuppertaler Richard from whom I stole this example…

Name: Basic predicate

ID: cs_plan13688_ld9506_30103_tableSearch1_pop1

Summary: MANDT = ‘800’

Execution Time (Self): 0 ms

Execution Time (Inclusive): 0 ms

Execution Start Time: 162,707 ms

Execution End Time: 162,707 ms

Estimated result size: 51

Estimation time: 0.067 ms

Inverted Index: not used

applied column filters (WHERE condition and analytic privileges) shown at the object where it is applied

Unfortunately there’s no time for now to do more tests – but I’m quite happy to see that so many improvement requests have been fulfilled (thanks for all the due follow up Mr. K!).

Session grouping, filtering and aggregation features

Fancy graphical display of memory and resource allocation/usage

Mhmm… I like pie…

Impressive how these lines have different colors and go upwards…

Statisticsserver process now included in indexserver process

Yep, it’s not a separate process any longer, doesn’t have it’s own persistence any more and is much better configurable (that’s what I heard).

Details are to be found in SAP note 1917938.

As it’s not a very visual feature, here some trace file output line from the migration 🙂 …


 i STATS_CTRL       NameServerControllerThread.cpp(00203) : installing...
 i STATS_CTRL       NameServerControllerThread.cpp(00299) : old StatisticsServer: ld9506:34205, volume: 2
 i STATS_CTRL       NameServerControllerThread.cpp(00355) : waiting for start of old StatisticsServer ld9506:34205, volume: 2...
 i STATS_CTRL       NameServerControllerThread.cpp(00371) : waiting for old StatisticsServer ld9506:34205, volume: 2 to stop all operations...
 i STATS_CTRL       NameServerControllerThread.cpp(00373) : old StatisticsServer ld9506:34205, volume: 2 has stopped all operations
 i STATS_CTRL       NameServerControllerThread.cpp(00376) : old StatisticsServer is ready. starting...
 i STATS_CTRL       CallInterfaceProxy.cpp(00035) : sending install request
 i FileIO           FileStatistics.cpp(00287) : FileFactoryConfiguration::initial(path="/usr/sap/WUP/HDB42/backup/log/", AsyncWriteSubmitActive=auto,AsyncWriteSubmitBlocks=new,AsynReadSubmit=off,#SubmitQueues=1,#CompletionQueues=1)
 i STATS_CTRL       CallInterfaceProxy.cpp(00039) : response to install request: OK
 i STATS_CTRL       NameServerControllerThread.cpp(00299) : old StatisticsServer: ld9506:34205, volume: 2
 i STATS_CTRL       NameServerControllerThread.cpp(00422) : found old StatisticsServer: ld9506:34205, volume: 2, will remove it
 i STATS_CTRL       NameServerControllerThread.cpp(00425) : forcing log backup...
 i FileIO           FileStatistics.cpp(00287) : FileFactoryConfiguration::initial(path="/usr/sap/WUP/HDB42/backup/log/", AsyncWriteSubmitActive=auto,AsyncWriteSubmitBlocks=new,AsynReadSubmit=off,#SubmitQueues=1,#CompletionQueues=1)
 i STATS_CTRL       NameServerControllerThread.cpp(00430) : log backup done. Reply: [OK]
--
[OK]
--
 i STATS_CTRL       NameServerControllerThread.cpp(00433) : stopping hdbstatisticsserver...
 i STATS_CTRL       NameServerControllerThread.cpp(00458) : waiting 5 seconds for stop...
 i Service_Shutdown TREXNameServer.cpp(03734) : setStopping(statisticsserver@ld9506:34205)
 i STATS_CTRL       NameServerControllerThread.cpp(00463) : hdbstatisticsserver stopped
 i STATS_CTRL       NameServerControllerThread.cpp(00466) : remove service from topology...
 i STATS_CTRL       NameServerControllerThread.cpp(00470) : service removed from topology
 i STATS_CTRL       NameServerControllerThread.cpp(00472) : remove volume 2 from topology...
 i STATS_CTRL       NameServerControllerThread.cpp(00476) : volume removed from topology
 i STATS_CTRL       NameServerControllerThread.cpp(00478) : mark volume 2 as forbidden...
 i STATS_CTRL       NameServerControllerThread.cpp(00480) : volume marked as forbidden
 i STATS_CTRL       NameServerControllerThread.cpp(00482) : old StatisticsServer successfully removed
 i STATS_CTRL       NameServerControllerThread.cpp(00405) : removing old section from statisticsserver.ini: statisticsserver_array_BACKUP_LAST_DATA
[...]
 i STATS_CTRL       NameServerControllerThread.cpp(00405) : removing old section from statisticsserver.ini: statisticsserver_view_VOLUMES_OUT_OF_ORDER
 i STATS_CTRL       NameServerControllerThread.cpp(00409) : making sure old StatisticsServer is inactive statisticsserver.ini: statisticsserver_general, active=false
 i STATS_CTRL       NameServerControllerThread.cpp(00222) : installation done

New SQL functions

HASH_SHA256 – “Returns a 32 byte VARBINARY hash value of the concatenated arguments. The hash is calculated using a SHA256 algorithm.“

I guess that Jon and In-Sung Lee will like that…

Alright, admit it: you also have to blink to believe that all that made it into SPS 7, right?

It’s nearly as if Xmas was coming up… wait a minute…

Cheers and a great holiday season everybody!

Lars

To report this post you need to login first.

25 Comments

You must be Logged on to comment or reply to a post.

Vivek Singh Bhoj December 4, 2013 at 3:26 am

Hi Lars,

Its HANA 007 😉

Thanks for sharing another great document.

New features in Plan Viz are welcome esp inverted index usage

Very colorful display of Memory usage – Lumira Pies

Regards,

Vivek

like (0)
Kumar Mayuresh December 4, 2013 at 4:11 am

Hi Lars

Thanks for the update on HANA 007 🙂

Finally there is a document on HANA Performance Analysis 🙂

Regards

Kumar

like (0)
1. Lars Breddemann
  Post author
  December 4, 2013 at 10:00 am
  Hi Kumar,
  
  thanks and yep – just saw the document…
  
  What can I say… I’m not impressed 😏 .
  
  Well, seems there’s still demand for some more explanation around this topic.
  
  Cheers, Lars
  
  like (0)
  1. Former Member December 4, 2013 at 10:52 am
    
    Hi Lars,
    
    feel free to update it! The document says “Version 1.0 – 27-11-2013”. We are eagerly waiting!
    
    Regards,
    
    Mark
    
    like (0)
    1. Lars Breddemann
      Post authorDecember 4, 2013 at 11:18 am
      
      Hehehe… let’s see about it.
      
      Currently writing on something much bigger, so first things first!
      
      Maybe 2014 will see me writing even more. Who knows 😀 .
      
      like (0)
2. Kumar Mayuresh December 5, 2013 at 1:53 am
  
  Hi Lars
  
  Awaiting for the bigger update, and at-least there is a document on performance analysis 😉 .
  
  I agree with you – over the content in the document.
  
  Regards
  
  Kumar 🙂
  
  like (0)
Former Member December 4, 2013 at 6:09 am

Thanks once again for introduction to SP7 in Bond style….. i loved it 🙂 🙂

Regards,

Kulwinder

like (0)
1. Lars Breddemann
  Post author
  December 4, 2013 at 10:01 am
  Awesome, up to now the comment section has an average of (2+2+1/3 =) 1,667 smileys which I consider pretty good for such a dry nerdy topic.
  
  If everybody is happy, how could I complain? 😀
  
  Cheers, Lars
  
  like (0)
Raj Kumar Salla
December 4, 2013 at 7:44 am
Hi Lars,

Thanks for SPS Bond updates. The term is nicely coined.

Hope in couple of weeks in the discussion threads the person might mention “I am using Bond version” 🙂

Regards

Raj

like (0)
Rama Shankar December 4, 2013 at 3:35 pm

Thanks Lars! 🙂

Rama Shankar

like (0)
Gregory Misiorek December 4, 2013 at 5:44 pm

is this on 1000+ GB RAM or is it just me?

like (0)
1. Lars Breddemann
  Post author
  December 4, 2013 at 6:14 pm
  Sorry, but I don’t get what you are referring to.
  
  Could you rephrase the question?
  
  like (0)
  1. Gregory Misiorek December 4, 2013 at 11:41 pm
    
    i had an impression that one of the graphics is showing a system with 1 033 961 MB physical memory, which if google calculates it correctly, comes down to 1 009.7 GB.
    
    that’s almost 30 times more than we, mortals, can rent on aws.
    
    like (0)
    1. Gregory Misiorek December 5, 2013 at 12:35 am
      
      uhm…15 times…
      
      like (0)
      1. Lars Breddemann
        Post authorDecember 5, 2013 at 8:01 am
        
        Ah, I see 🙂
        
        Well, this system actually has one TB of RAM. It’s a physical single node HANA server – about two years old.
        
        like (0)
John Appleby
December 5, 2013 at 3:30 pm
Hey Lars,

SP7 seems to bring some nice new things for us. Plenty of work to do of course for SP8 🙂

John

like (0)
Denys Kempen
January 2, 2014 at 4:08 pm
Hi Lars,

Thank you for the inspiration. Hope you don’t mind I quoted you in the SAP HANA Academy blog on What’s New with monitoring. 😉

Denys

like (0)
Ingo Peter
April 15, 2014 at 7:20 pm
Hi Lars,

Resource Utilization and Memory Overview seem to be rather secret features: I could never launch them – even not as system user in a freshly installed system:

Which privilege do I actually need?

Regards,

Ingo

like (0)
1. Denys Kempen
  April 15, 2014 at 7:29 pm
  Hi Ingo,
  
  All you need is the SAP HANA Academy. 😉
  
  See video
  
  Monitoring SAP HANA – Monitoring Role | SAP HANA
  
  like (0)
Former Member June 15, 2014 at 7:16 am

Hi Lars,

Regarding:

Statisticsserver process now included in indexserver process

What was the stat server + index server peak memory utilization?

what was the retention period set?

Did you observed any OOM/Unloads on the master node after migration?

We are having a big time issues with 72.

like (0)
1. Lars Breddemann
  Post author
  June 15, 2014 at 11:26 am
  Hi Srikanth
  
  After the migration there is no way to distinguish memory requirement of the statistics service from the other sessions running in the indexserver process.
  
  There is no separate statistics server process after the migration anymore.
  
  If you face issues due to this – actually the statistics service integration should help _reducing_ resource usage – please have support look into it.
  
  Besides that, rev 72 should be upgraded to the current maintenance revision for SPS 7: 74.02.
  
  – Lars
  
  like (0)
2. John Appleby
  June 15, 2014 at 12:20 pm
  What we found matches what Lars says: integrated statistics server reduces memory consumption by 2-3GB.
  
  Also Rev.72, 73 were problematic – update to 74.2/80 which are better in many ways!
  
  If you get OOM are you sure you are not just low on memory?
  
  like (0)
  1. Former Member June 15, 2014 at 4:41 pm
    
    2-3 Gig not gonna help, we have implemented retention procedure and set it to 7 days but we see a Stats process resident size of 90 to 120 gig.
    Hopefully after migrating to 80, we might see some improvement.
    
    like (0)
    1. John Appleby
      June 15, 2014 at 4:45 pm
      
      Sounds like you have a problem, best to take this up with OSS. It’s tough to troubleshoot this sort of thing in a public forum!
      
      like (0)
    2. Lars Breddemann
      Post authorJune 15, 2014 at 5:30 pm
      
      As long as you see a statistics server process you *don’t* use the integrated statistics service.
      
      Not sure where this went wrong, but there really is no additional process left after the migration.
      
      like (0)