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

asked 15 Apr '11, 12:14

Martin's gravatar image

Martin
9.0k127166257
accept rate: 14%


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.

permanent link

answered 15 Apr '11, 12:34

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

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)

(15 Apr '11, 12:41) Martin
Replies hidden
2

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.

(18 Apr '11, 11:35) Glenn Paulley

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"?

(08 Aug '11, 04:38) Arthoor
2

@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.

(08 Aug '11, 10:16) Glenn Paulley
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×273

question asked: 15 Apr '11, 12:14

question was seen: 2,024 times

last updated: 08 Aug '11, 10:16