I should have written something down about this 3 years ago.   That’s when I made a comment to a friend at the ISUG Tech conference that expected_row_size isn’t exactly what you might think it is.  In fact, even if you set it to it’s maximum allowable size, you can still see forwarded rows happen.  And, that’s really what the docs say.  Well, it’s subtle anyway.    Then, at this past year’s conference (2015), I see it mentioned in a couple of presentations.  That prompted me to go find my old research, and try to dig a little deeper.

As a result, there is too much information to really make effective use of a “blog” entry, so I decided to create a series of videos on the subject.

The first video

SAP ASE 16 – What to Expect from Expected Row Size – Part 1 – YouTube

just discusses the topic and it’s current documentation.

The second video

SAP ASE 16 – What to Expect from Expected Row Size – Part 2 – YouTube

goes into a demonstration and case study of the various behaviors, metrics, and how transactions can affect what rows get forwarded, and what rows stay on the page.

Space management is an interesting (and complicated) topic.  I’m sure those here who have access to the ASE code can elaborate more on the topic, but these are simply my observations from “the outside”.  Comments are welcomed!

To report this post you need to login first.

3 Comments

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

  1. Jeff Tallman

    NCFS = non contiguous free space as you surmised.   However, on the row “garbage collection” – what was your ‘enable housekeeper GC’ setting……unless it was 0, it is possible that the garbage collection you saw was actually the HKGC rewriting the page in-between your inserts…..which is why at the end you didn’t see it do so for the “row 5” of the row 4+row 5 txn.   Easily proven by turning HKGC off by setting it to 0 (not recommended in production btw in case other readers jump on this).   One aspect to keep in mind is under APL, the txn that modified the page also did the garbage collection on the page – which slowed it down some.   For DOL, the HKGC does the garbage collection, which means the txn can do the modifications faster.  As far as the rest, probably, the shift from row 4 moved the rows too far down the page that it couldn’t shift anymore for row 5 (last row offset was >1800) – hence row 5 couldn’t shift more…   At first I also thought you were using datapage locking because of the shift – but went back and saw the datarows – so obviously, ASE is taking advantage of the MASS bit to allow the shifting.   I suspect, though, that it isn’t always possible….

    (0) 
    1. Kevin Sherlock Post author

      Thanks Jeff,

      I meant to include “enable housekeeper GC” setting in video but forgot.  For that example, it was set at ‘4’.  I have other examples for a future video which i change this setting (including setting it to 0) and perform more tests.

      In any case, I simply just meant to show that row forwarding can happen in some circumstances even when ers is set to max setting.  I would imagine that when shifting is not possible, it would increase the probability for row forwarding to occur.

      More to come.

      (0) 
    2. Kevin Sherlock Post author

      BTW Jeff.  My test results in that scenario are exactly the same with “enable housekeeper GC” set to ‘0’.   Same results as when set to ‘4’.

      (0) 

Leave a Reply