What is the meaning of row segments from sa_table_fragmentation?

[MCMartin]

What is the meaning of row segments in the output of sa_table_fragmentation.

[Former Member · Accepted solution]

A row segment is a portion of a row that is not stored contiguously with the row header, or other row segments, for the same row. "Not stored contiguously" means a separate database page.

In SQL Anywhere, there is no limit on the size of a row (though a row is limited to 45K columns). If you had 4K pages, then rows that average 8K would (probably) consist of (roughly) two row segments each. However, if rows grow - because of updates - then the server may have no choice but to split a row segment into two, because there may not be enough room on the same page to store the longer values.

[MCMartin · Accepted solution]

So with your example of 8k rows the segs_per_row will be >1.1 but not necessarily indicate a fragmentation (or at least one which could be avoided)

[Former Member · Accepted solution]

Right - having a segs_per_row higher than 1 doesn't necessarily indicate a problem, particularly if the rows are long. However, if you have relatively short rows that should be stored contiguously, and you see a segs_per_row value much higher than 1, then you probably have internal fragmentation within the table pages, and it's time for an unload/reload or REORGANIZE TABLE.

[former_SQLA_member1694875 · Accepted solution]

Would there be any positive changes in performance if I reorganize a table which has 1,06 (or even less) segs_per_row? The table has only 9 columns and currently about 47K rows. What is the value that can be named "much higher than 1"?

[Former Member · Accepted solution]

@Arthoor - no, I would expect little or no difference. I would not consider executing a REORGANIZE statement unless the segs_per_row value was at least 1.3 or 1.4 or higher.