SPS 8: Memory Usage of Expensive Statements

[Former Member]

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

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