Skip to Content

An interesting new feature of SPS8 is now the ability to easily see the temporary memory used by a long running statement.

To activate this feature you need to switch on ‘enable_tracking’ and ‘memory_tracking’ in the global.ini file:

Then activate the ‘Expensive Statement Trace’ as normal:


Now execute your long running query.


Unfortunately In the standard Expensive Statements Trace Log I couldn’t see the new field ‘MEMORY_SIZE’


Fortunately it can be seen when you run a SQL on the M_EXPENSIVE_STATEMENTS directly:

select “STATEMENT_STRING”,

          round(MEMORY_SIZE/1024/1024/1024,2) as “Memory Size Gb”,

          round(DURATION_MICROSEC/1000000,2) as “Seconds”, *

from  M_EXPENSIVE_STATEMENTS

where “OPERATION” = ‘AGGREGATED_EXECUTION’

order by statement_start_time

limit 10;



I used it to compare a heavy Join (10 Million reference document line items  to 10 Million line items) in separate tables with  3 similar queries.

Option:

1) Calculation View (CA_PERF_GRA_001)

2) Calculation View [SQL Engine]  (CA_PERF_GRA_002)

3) Direct SQL Statement

trace options.jpg


Option 1)   performed the worst both in terms of Speed and Temporary memory usage.

My example data set is available to create on an earlier blog JOIN’s in an Imperfect World

Option 1 & 2)   were defined as:

The only difference between the two is the ‘Execute In’   setting in the properties. 

CA_PERF_GRA_001  is set as the DEFAULT <BLANK>

CA_PERF_GRA_002  is set as ‘SQL Engine


Option 3 was the SQL:

select SUM(“XVAL”),

       SUM(“YVAL”),

       count(*)

from “PERFORMANCE”.”PERF_X” as x

LEFT OUTER JOIN “PERFORMANCE”.”PERF_Y” as y

    ON  x.”REFDOC”   = y.”YDOC”

    and x.”REFDOCLN” = y.”YDOCLN”

LEFT OUTER JOIN “PERFORMANCE”.”PERF_Z” as z

    ON y.”ZKEY” = z.”ZKEY”;

So in this simple scenario the joint winners with

Duration ~3.2 seconds

Temporary Memory 0.58 Gb

were:

2) Calculation View [SQL Engine]  (CA_PERF_GRA_002)

3) Direct SQL Statement



In a distance last place was the default calculation view with

Duration ~15 seconds

Temporary Memory 3.5 Gb

1) Calculation View (CA_PERF_GRA_001)



Test Out Your  Queries today

To report this post you need to login first.

14 Comments

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

  1. Lars Breddemann

    Hey Aaron

    I like this blog post!

    Self made experiences, nicely written up, no “I re-write the documentation in my own words” parts… – just what I want to read 🙂 .

    Thanks for that.

    Did you manage to take a PlanViz of the SQL-Engine enabled executions?

    From the join construct I have the impression that using the OLAP engine would be a good choice here and I suspect that this is what’s happening.

    Also: the linked screenshots don’t show up unless you click on them.

    Not sure if this is a jive bug, but could you upload the pics to SCN instead, please?

    Would make reading it much easier.

    – Lars

    (0) 
    1. Aron MacDonald Post author

      Thanks Lars.

      Oddly I don’t see a problem with the graphics with Safari or Chrome (I’ve not tried others).

      I’m not familiar with Jive so can you let me know if you also experience a problem with other browsers.

      I unfortunately didn’t capture a PlanViz yet, but there’s also a lot of nice new features with PlanViz in SPS8 so definitely worth exploring. When I have a chance I’ll drop in a screenshot.

      If, in the meantime, someone is curious then the below link has the SQL statements to re-create the exact same dataset:

      trace memory – Google Docs

      (0) 
      1. Lars Breddemann

        Unfortunately it’s the same with other browsers.

        I do remember having a similar issue with linked pictures in one of my blogs – uploading to JIVE was the only way for me to fix it.

        Interestingly, the pictures _do_ show up, when clicking on the linked areas – but these areas are just blank in the normal view. 🙁

        About PlanViz: correct there are a couple of newly released visual effects, e.g. like execution node indication and network transfer as edges.

        Let’s see, maybe I can pull together a little post on it this week.

        Thanks again for this one. Cheers, Lars

        (0) 
        1. Aron MacDonald Post author

          That’s weird, I just tested with a few colleagues machine with IE  and it was also fine.

          I then tried inserting an image from the file system a different way, and got the reverse problem. It would appear blurry on my screen until I clicked on it.

          BTW this link also has the images on the same document trace memory – Google Docs

          If anyone one else has a problem viewing please let me know. 

          (0) 
  2. Kumar Mayuresh

    Hi Aron,

    I just checked with SPS 07, and I can see the same feature on SPS 07 too. Please refer: the screen shots:-

    2.JPG

    1.JPG

    Since the feature is present in SPS 07 too, I believe it should work fine in SPS 07. or is there any other think which I need to take care while activating and using the same ?

    Regards

    Kumar 🙂

    (0) 
    1. Lars Breddemann

      Hi Kumar

      that’s right the parameter was there in SPS 7 but it was not released, because it was not fully implemented and potentially problematic.

      Thus, as with all the undocumented parameters: touch that dial on your own risk.

      – Lars

      (0) 
      1. Kumar Mayuresh

        Hi Aron ,

        Yes MEMORY_SIZE gets populated, with the expensive statements. 🙂

        Though as Lars stated it was not implemented completely and is potentially problematic, I will prefer turning it back to default state 🙂 .

        Regards

        Kumar.

        (0) 
  3. peter kim

    This is great to know and see – but I am curious to know why there is such a HUGE difference? Without the upgrade and tool – how would you have known the solution and the large difference?

    Thanks!

    (0) 
    1. Lars Breddemann

      Hi Peter

      the difference in memory consumption is due to the different processing steps (build up of large intermediate result sets vs. less temp result sets).

      Without such a tool you could use PlanViz to analyze CPU and memory usage.

      But keep in mind that with SAP HANA new features – also in monitoring and system analysis – are delivered with every SPS. So there really is no “without the upgrade” for a long time 🙂

      Cheers,

      Lars

      (0) 
  4. Rodrigo Monge

    Hi Aron, great post! I’ve been trying to enable this feature to keep track of memory used by some of my queries. However I get no values for memory size column when I query  M_EXPENSIVE_STATEMENTS table, did I miss anything? Any clue?

    Thanks in advance

    hana’s rev is 82

    /wp-content/uploads/2014/09/hanasettings_552591.png

    (0) 

Leave a Reply