Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

datarows and updates

6 posts in Performance and Tuning Last posting was on 2012-11-30 17:27:02.0Z
Luc Van der Veurst Posted on 2012-11-08 07:15:00.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.performance+tuning
Subject: datarows and updates
Lines: 34
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
X-RFC2646: Format=Flowed; Original
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <509b5bf4$1@forums-1-dub>
Date: 7 Nov 2012 23:15:00 -0800
X-Trace: forums-1-dub 1352358900 172.20.134.152 (7 Nov 2012 23:15:00 -0800)
X-Original-Trace: 7 Nov 2012 23:15:00 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13290
Article PK: 91781

Hello,

I'v been trying to find the answer to the following statements/questions in
the docs,
but without success.

When a table has datarows locking, what happens on a page when an update
occurs
and :

1) the size of the row increases:

the current space of the row is marked as logical deleted, and the row
is placed
elsewhere (on the same page if possible, on another one if not) ?

2) the size of the row decreases :

the same space of the tuple is used ? then what happens to the remaining
space (marked
as logical deleted, or staying with the record, so that it can be used
when the row is
updated again and needs more space) ?

I'm converting some tables to RLL and I think it would be best to initially
fill the columns
with values that won't increase in size where possible.
(f.i. using default values instead of NULLs).

Thanks,
Luc.


Rob V Posted on 2012-11-08 09:38:45.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:16.0) Gecko/20121026 Thunderbird/16.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: datarows and updates
References: <509b5bf4$1@forums-1-dub>
In-Reply-To: <509b5bf4$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <509b7da5@forums-1-dub>
Date: 8 Nov 2012 01:38:45 -0800
X-Trace: forums-1-dub 1352367525 172.20.134.152 (8 Nov 2012 01:38:45 -0800)
X-Original-Trace: 8 Nov 2012 01:38:45 -0800, vip152.sybase.com
Lines: 85
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13291
Article PK: 91782


On 08-Nov-2012 08:15, Luc Van der Veurst wrote:
> Hello,
>
> I'v been trying to find the answer to the following statements/questions in
> the docs,
> but without success.
>
> When a table has datarows locking, what happens on a page when an update
> occurs
> and :
>
> 1) the size of the row increases:
>
> the current space of the row is marked as logical deleted, and the row
> is placed
> elsewhere (on the same page if possible, on another one if not) ?
>
> 2) the size of the row decreases :
>
> the same space of the tuple is used ? then what happens to the remaining
> space (marked
> as logical deleted, or staying with the record, so that it can be used
> when the row is
> updated again and needs more space) ?
>
> I'm converting some tables to RLL and I think it would be best to initially
> fill the columns
> with values that won't increase in size where possible.
> (f.i. using default values instead of NULLs).
>
> Thanks,
> Luc.
>
>
>

DOL tables are different from APL tables in various ways, one of these
being that there can be empty space between data rows on a DOL page
(that never happens for APL). So you can have: row1...some empty
bytes... row2...row3..some empty bytes...row4 (etc). The internal temr
for this is NTFS (non-contiguous free space).

As for your questions, (1) if a row increases in length and it fits on
the current location (i.e. there is sufficient empty space directly
behind it), it will extend into here. If it doesn't fit, the row is
placed on a different page and a short row forward marker is set at the
original location, leaving the rest of the original row as empty space.
(2) when a row shrinks, that means the left-over space will be empty
bytes between the row and the next row.

A row gets logically deleted as a result of a delete operation (i.e. no
row forwarding marker is placed).

Over time, the amount of logically deleted rows and NTFS grow and causes
less efficient storage.
Cleaning up the logically deleted rows and NTFS is done by running
'reorg reclaim_space' which rearranges the data rows without any
interleaving free space on the page (this works only on a single page at
a time and does not defragment a table by operating on rows across
pages, as is often, and incorrectly, thought).
Cleaning up forwarded rows is done with 'reorg forwarded_rows' (since
reading a forwarded rows takes 1 extra I/O). 'reorg compact' does both
of these.
When pages have become completely empty because all rows have been
deleted, the housekeeper 'HK GC' task cleans it up and deallocates the
page. How it does this depends on the setting of 'enable housekeeper GC'.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Luc Van der Veurst Posted on 2012-11-08 11:50:39.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <509b5bf4$1@forums-1-dub> <509b7da5@forums-1-dub>
Subject: Re: datarows and updates
Lines: 105
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
X-RFC2646: Format=Flowed; Response
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <509b9c8f$1@forums-1-dub>
Date: 8 Nov 2012 03:50:39 -0800
X-Trace: forums-1-dub 1352375439 172.20.134.152 (8 Nov 2012 03:50:39 -0800)
X-Original-Trace: 8 Nov 2012 03:50:39 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13292
Article PK: 91783

Thanks, Rob.

So it works like I expected.

It would be good to have a kind of a fill factor for inserting rows in a RLL
table,
so that with each insert, some extra bytes would be reserved for updates
that
will increase the size of the row.

Luc.

"Rob V" <rob@sypron.nl> wrote in message news:509b7da5@forums-1-dub...
> On 08-Nov-2012 08:15, Luc Van der Veurst wrote:
>> Hello,
>>
>> I'v been trying to find the answer to the following statements/questions
>> in
>> the docs,
>> but without success.
>>
>> When a table has datarows locking, what happens on a page when an update
>> occurs
>> and :
>>
>> 1) the size of the row increases:
>>
>> the current space of the row is marked as logical deleted, and the
>> row
>> is placed
>> elsewhere (on the same page if possible, on another one if not) ?
>>
>> 2) the size of the row decreases :
>>
>> the same space of the tuple is used ? then what happens to the
>> remaining
>> space (marked
>> as logical deleted, or staying with the record, so that it can be
>> used
>> when the row is
>> updated again and needs more space) ?
>>
>> I'm converting some tables to RLL and I think it would be best to
>> initially
>> fill the columns
>> with values that won't increase in size where possible.
>> (f.i. using default values instead of NULLs).
>>
>> Thanks,
>> Luc.
>>
>>
>>
>
> DOL tables are different from APL tables in various ways, one of these
> being that there can be empty space between data rows on a DOL page (that
> never happens for APL). So you can have: row1...some empty bytes...
> row2...row3..some empty bytes...row4 (etc). The internal temr for this is
> NTFS (non-contiguous free space).
>
> As for your questions, (1) if a row increases in length and it fits on the
> current location (i.e. there is sufficient empty space directly behind
> it), it will extend into here. If it doesn't fit, the row is placed on a
> different page and a short row forward marker is set at the original
> location, leaving the rest of the original row as empty space.
> (2) when a row shrinks, that means the left-over space will be empty bytes
> between the row and the next row.
>
> A row gets logically deleted as a result of a delete operation (i.e. no
> row forwarding marker is placed).
>
> Over time, the amount of logically deleted rows and NTFS grow and causes
> less efficient storage.
> Cleaning up the logically deleted rows and NTFS is done by running 'reorg
> reclaim_space' which rearranges the data rows without any interleaving
> free space on the page (this works only on a single page at a time and
> does not defragment a table by operating on rows across pages, as is
> often, and incorrectly, thought).
> Cleaning up forwarded rows is done with 'reorg forwarded_rows' (since
> reading a forwarded rows takes 1 extra I/O). 'reorg compact' does both of
> these.
> When pages have become completely empty because all rows have been
> deleted, the housekeeper 'HK GC' task cleans it up and deallocates the
> page. How it does this depends on the setting of 'enable housekeeper GC'.
>
> --
> HTH,
>
> Rob V.
> -----------------------------------------------------------------
> Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication Server
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks & Recipes for Sybase ASE"
> "The Complete Sybase IQ Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
> Sypron B.V., The Netherlands | Chamber of Commerce 27138666
> -----------------------------------------------------------------


Rob V Posted on 2012-11-08 12:09:13.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:16.0) Gecko/20121026 Thunderbird/16.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: datarows and updates
References: <509b5bf4$1@forums-1-dub> <509b7da5@forums-1-dub> <509b9c8f$1@forums-1-dub>
In-Reply-To: <509b9c8f$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <509ba0e9$1@forums-1-dub>
Date: 8 Nov 2012 04:09:13 -0800
X-Trace: forums-1-dub 1352376553 172.20.134.152 (8 Nov 2012 04:09:13 -0800)
X-Original-Trace: 8 Nov 2012 04:09:13 -0800, vip152.sybase.com
Lines: 114
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13293
Article PK: 91784


On 08-Nov-2012 12:50, Luc Van der Veurst wrote:
> Thanks, Rob.
>
> So it works like I expected.
>
> It would be good to have a kind of a fill factor for inserting rows in a RLL
> table,
> so that with each insert, some extra bytes would be reserved for updates
> that
> will increase the size of the row.
>
> Luc.
>
> "Rob V" <rob@sypron.nl> wrote in message news:509b7da5@forums-1-dub...
>> On 08-Nov-2012 08:15, Luc Van der Veurst wrote:
>>> Hello,
>>>
>>> I'v been trying to find the answer to the following statements/questions
>>> in
>>> the docs,
>>> but without success.
>>>
>>> When a table has datarows locking, what happens on a page when an update
>>> occurs
>>> and :
>>>
>>> 1) the size of the row increases:
>>>
>>> the current space of the row is marked as logical deleted, and the
>>> row
>>> is placed
>>> elsewhere (on the same page if possible, on another one if not) ?
>>>
>>> 2) the size of the row decreases :
>>>
>>> the same space of the tuple is used ? then what happens to the
>>> remaining
>>> space (marked
>>> as logical deleted, or staying with the record, so that it can be
>>> used
>>> when the row is
>>> updated again and needs more space) ?
>>>
>>> I'm converting some tables to RLL and I think it would be best to
>>> initially
>>> fill the columns
>>> with values that won't increase in size where possible.
>>> (f.i. using default values instead of NULLs).
>>>
>>> Thanks,
>>> Luc.
>>>
>>>
>>>
>>
>> DOL tables are different from APL tables in various ways, one of these
>> being that there can be empty space between data rows on a DOL page (that
>> never happens for APL). So you can have: row1...some empty bytes...
>> row2...row3..some empty bytes...row4 (etc). The internal temr for this is
>> NTFS (non-contiguous free space).
>>
>> As for your questions, (1) if a row increases in length and it fits on the
>> current location (i.e. there is sufficient empty space directly behind
>> it), it will extend into here. If it doesn't fit, the row is placed on a
>> different page and a short row forward marker is set at the original
>> location, leaving the rest of the original row as empty space.
>> (2) when a row shrinks, that means the left-over space will be empty bytes
>> between the row and the next row.
>>
>> A row gets logically deleted as a result of a delete operation (i.e. no
>> row forwarding marker is placed).
>>
>> Over time, the amount of logically deleted rows and NTFS grow and causes
>> less efficient storage.
>> Cleaning up the logically deleted rows and NTFS is done by running 'reorg
>> reclaim_space' which rearranges the data rows without any interleaving
>> free space on the page (this works only on a single page at a time and
>> does not defragment a table by operating on rows across pages, as is
>> often, and incorrectly, thought).
>> Cleaning up forwarded rows is done with 'reorg forwarded_rows' (since
>> reading a forwarded rows takes 1 extra I/O). 'reorg compact' does both of
>> these.
>> When pages have become completely empty because all rows have been
>> deleted, the housekeeper 'HK GC' task cleans it up and deallocates the
>> page. How it does this depends on the setting of 'enable housekeeper GC'.
>>
>> --
>> HTH,
>>
>> Rob V.
>> -----------------------------------------------------------------
>> Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication Server
>>
>> Author of Sybase books (order online at www.sypron.nl/shop):
>> "Tips, Tricks & Recipes for Sybase ASE"
>> "The Complete Sybase IQ Quick Reference Guide"
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
>> Sypron B.V., The Netherlands | Chamber of Commerce 27138666
>> -----------------------------------------------------------------
>
>

I think that's what exp_row_size is for. But you should probably check
out the details in the P&T Guide as I do not have them handy.

HTH,

Rob V.


Luc Van der Veurst Posted on 2012-11-08 13:29:46.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <509b5bf4$1@forums-1-dub> <509b7da5@forums-1-dub> <509b9c8f$1@forums-1-dub> <509ba0e9$1@forums-1-dub>
Subject: Re: datarows and updates
Lines: 134
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
X-RFC2646: Format=Flowed; Response
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <509bb3ca$1@forums-1-dub>
Date: 8 Nov 2012 05:29:46 -0800
X-Trace: forums-1-dub 1352381386 172.20.134.152 (8 Nov 2012 05:29:46 -0800)
X-Original-Trace: 8 Nov 2012 05:29:46 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13294
Article PK: 91786


"Rob V" <rob@sypron.nl> wrote in message news:509ba0e9$1@forums-1-dub...
> On 08-Nov-2012 12:50, Luc Van der Veurst wrote:
>> "Rob V" <rob@sypron.nl> wrote in message news:509b7da5@forums-1-dub...
>>> On 08-Nov-2012 08:15, Luc Van der Veurst wrote:
>>>> Hello,
>>>>
>>>> I'v been trying to find the answer to the following
>>>> statements/questions
>>>> in
>>>> the docs,
>>>> but without success.
>>>>
>>>> When a table has datarows locking, what happens on a page when an
>>>> update
>>>> occurs
>>>> and :
>>>>
>>>> 1) the size of the row increases:
>>>>
>>>> the current space of the row is marked as logical deleted, and
>>>> the
>>>> row
>>>> is placed
>>>> elsewhere (on the same page if possible, on another one if not)
>>>> ?
>>>>
>>>> 2) the size of the row decreases :
>>>>
>>>> the same space of the tuple is used ? then what happens to the
>>>> remaining
>>>> space (marked
>>>> as logical deleted, or staying with the record, so that it can be
>>>> used
>>>> when the row is
>>>> updated again and needs more space) ?
>>>>
>>>> I'm converting some tables to RLL and I think it would be best to
>>>> initially
>>>> fill the columns
>>>> with values that won't increase in size where possible.
>>>> (f.i. using default values instead of NULLs).
>>>>
>>>> Thanks,
>>>> Luc.
>>>>
>>>>
>>>>
>>>
>>> DOL tables are different from APL tables in various ways, one of these
>>> being that there can be empty space between data rows on a DOL page
>>> (that
>>> never happens for APL). So you can have: row1...some empty bytes...
>>> row2...row3..some empty bytes...row4 (etc). The internal temr for this
>>> is
>>> NTFS (non-contiguous free space).
>>>
>>> As for your questions, (1) if a row increases in length and it fits on
>>> the
>>> current location (i.e. there is sufficient empty space directly behind
>>> it), it will extend into here. If it doesn't fit, the row is placed on a
>>> different page and a short row forward marker is set at the original
>>> location, leaving the rest of the original row as empty space.
>>> (2) when a row shrinks, that means the left-over space will be empty
>>> bytes
>>> between the row and the next row.
>>>
>>> A row gets logically deleted as a result of a delete operation (i.e. no
>>> row forwarding marker is placed).
>>>
>>> Over time, the amount of logically deleted rows and NTFS grow and causes
>>> less efficient storage.
>>> Cleaning up the logically deleted rows and NTFS is done by running
>>> 'reorg
>>> reclaim_space' which rearranges the data rows without any interleaving
>>> free space on the page (this works only on a single page at a time and
>>> does not defragment a table by operating on rows across pages, as is
>>> often, and incorrectly, thought).
>>> Cleaning up forwarded rows is done with 'reorg forwarded_rows' (since
>>> reading a forwarded rows takes 1 extra I/O). 'reorg compact' does both
>>> of
>>> these.
>>> When pages have become completely empty because all rows have been
>>> deleted, the housekeeper 'HK GC' task cleans it up and deallocates the
>>> page. How it does this depends on the setting of 'enable housekeeper
>>> GC'.
>>
>> Thanks, Rob.
>>
>> So it works like I expected.
>>
>> It would be good to have a kind of a fill factor for inserting rows in a
>> RLL
>> table,
>> so that with each insert, some extra bytes would be reserved for updates
>> that
>> will increase the size of the row.
>>
>> Luc.
>>
>
> I think that's what exp_row_size is for. But you should probably check out
> the details in the P&T Guide as I do not have them handy.
>
> HTH,
>
> Rob V.

OK, I found it :

sp_chgattribute objname, {"max_rows_per_page" | "fillfactor" |
"reservepagegap" | "exp_row_size"
concurrency_opt_threshold | "optimistic_index_lock" | "identity_burn_max"},
value, optvalue

exp_row_size reserves a specified amount of space for the rows in data-only
locked tables. Use this option to reduce the number of rows being forwarded,
which can be expensive during updates. Valid values are 0, 1, and any value
between the minimum and maximum row length for the table. 0 means a
server-wide setting is applied, and 1 means to fully pack the rows on the
data pages.

Default value

If you do not specify an expected row size or a value of 0 when you create a
data-only-locked table with variable-length columns, Adaptive Server uses
the amount of space specified by the configuration parameter default
exp_row_size percent for any table that has variable-length columns.

The default is 5%.

Thanks,
Luc.


Sherlock, Kevin [TeamSybase] Posted on 2012-11-30 17:27:02.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <509b5bf4$1@forums-1-dub> <509b7da5@forums-1-dub>
Subject: Re: datarows and updates
Lines: 132
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50b8ec66$1@forums-1-dub>
Date: 30 Nov 2012 09:27:02 -0800
X-Trace: forums-1-dub 1354296422 172.20.134.152 (30 Nov 2012 09:27:02 -0800)
X-Original-Trace: 30 Nov 2012 09:27:02 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13308
Article PK: 1020106

Hi,
Slight clarification on what happens during an expanding update (update
which increases the size of a row):

1. If enough free space exists between the current row and the next row on
the page, it is updated/expanded in place
2. If not (1), and there is sufficient space on the current page for the
entire new row image, the existing row is marked as "orphaned/deleted", and
the new row image is placed at the "dol_freeoff" mark on the page (the space
at the end of the page that has room for a new row). This is not the same
as a forwarded row, but results in a "deleted/orphaned" row, and the row
offset table at the end of the page is updated. The row-id remains the same
as it does for all DOL operations.
3. If not (2), then the row is forwarded to another page, the home row
location is replaced by a pointer to the new row/page. At this point, the
algorithm to find a target page for the forwarded row is set into motion.
For the most part, a target page is attempted to be found on the current
extent, then current allocation unit, etc. This is where reservepgap
setting can be effective in keeping a "locality" to the forwarded row.

Note that updating an already forwarded row follows exactly as above, except
that if a forwarded row has to be forwarded again, the home row pointer is
changed, and the first forwarded row is marked as deleted. Also, if a
forwarded row is updated, the home page is checked to see if it can
accomodate the update and the row is "unforwarded" if it can be "re-homed"
as a result of the update.

Lots of course depends on various garbage collection settings, configuration
settings, etc, as Rob mentions.

Also, I believe Rob intended to type "NCFS" for the term given to the
"garbage" created by shrinking updates, deleted/orphaned, and fowarded rows.

Space managment is really a fascinating topic, and is deserving of a white
paper at some point because I believe that most DBA's don't realize what all
is involved and what the various space management properties can do to both
help, and in some cases hurt the process of managing space efficiently
depending on the workload of a table.

"Rob V" <rob@sypron.nl> wrote in message news:509b7da5@forums-1-dub...
> On 08-Nov-2012 08:15, Luc Van der Veurst wrote:
>> Hello,
>>
>> I'v been trying to find the answer to the following statements/questions
>> in
>> the docs,
>> but without success.
>>
>> When a table has datarows locking, what happens on a page when an update
>> occurs
>> and :
>>
>> 1) the size of the row increases:
>>
>> the current space of the row is marked as logical deleted, and the
>> row
>> is placed
>> elsewhere (on the same page if possible, on another one if not) ?
>>
>> 2) the size of the row decreases :
>>
>> the same space of the tuple is used ? then what happens to the
>> remaining
>> space (marked
>> as logical deleted, or staying with the record, so that it can be
>> used
>> when the row is
>> updated again and needs more space) ?
>>
>> I'm converting some tables to RLL and I think it would be best to
>> initially
>> fill the columns
>> with values that won't increase in size where possible.
>> (f.i. using default values instead of NULLs).
>>
>> Thanks,
>> Luc.
>>
>>
>>
>
> DOL tables are different from APL tables in various ways, one of these
> being that there can be empty space between data rows on a DOL page (that
> never happens for APL). So you can have: row1...some empty bytes...
> row2...row3..some empty bytes...row4 (etc). The internal temr for this is
> NTFS (non-contiguous free space).
>
> As for your questions, (1) if a row increases in length and it fits on the
> current location (i.e. there is sufficient empty space directly behind
> it), it will extend into here. If it doesn't fit, the row is placed on a
> different page and a short row forward marker is set at the original
> location, leaving the rest of the original row as empty space.
> (2) when a row shrinks, that means the left-over space will be empty bytes
> between the row and the next row.
>
> A row gets logically deleted as a result of a delete operation (i.e. no
> row forwarding marker is placed).
>
> Over time, the amount of logically deleted rows and NTFS grow and causes
> less efficient storage.
> Cleaning up the logically deleted rows and NTFS is done by running 'reorg
> reclaim_space' which rearranges the data rows without any interleaving
> free space on the page (this works only on a single page at a time and
> does not defragment a table by operating on rows across pages, as is
> often, and incorrectly, thought).
> Cleaning up forwarded rows is done with 'reorg forwarded_rows' (since
> reading a forwarded rows takes 1 extra I/O). 'reorg compact' does both of
> these.
> When pages have become completely empty because all rows have been
> deleted, the housekeeper 'HK GC' task cleans it up and deallocates the
> page. How it does this depends on the setting of 'enable housekeeper GC'.
>
> --
> HTH,
>
> Rob V.
> -----------------------------------------------------------------
> Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication Server
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks & Recipes for Sybase ASE"
> "The Complete Sybase IQ Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
> Sypron B.V., The Netherlands | Chamber of Commerce 27138666
> -----------------------------------------------------------------