Christian Kaufmann wrote:

> Hi,
>
> I always thought, that the fill ratio is corrected to about 80% when
> doing a restore.

It is well - and incorrectly - known that the fill ratio is 80%
if the database is set up to retain space for new versions. I've
read it often from sources who should know. But I've also read
the code and was around when it was designed. When space is
reserved, the system leaves space for one fragmented record header
for every primary record version stored on a page.

A primary record version is the newest version of a record - back
versions, blobs, and fragments don't count.

A fragmented record is one that is split across pages, either
because it was longer than a page or because it was forced to
be on a particular page and didn't fit completely there.

A fragmented record header is a normal record header plus a
pointer to the page and line of the fragmented part of the
data. Counting on my fingers, I think it's 22 bytes, of which
six plus three fill bytes occur only in fragmented records.

So, if your records compress to less than 9 bytes, you'll have
more than 50% reserved space.

Regards,


Ann


A fragmented record header consists of
a 4 byte transaction id,
a 4 byte value of the page on which the back version is stored,
a 2 byte value of the offset in the index on that page that
points to the back version on page,
a 2 byte flag,
1 byte of format version,
4 bytes of page where the rest of the record is stored,
2 bytes of the offset in the index on that page that
points to the data on page

That's 19 bytes plus three bytes to align the fragment page number,
so 22 bytes.