SQL Anywhere database - Table and Index Fragmentation

Author

Table and Index Fragmentation

mason

2005-04-11, 9:23 am

What are good indications that a table and an index become too fragmented?
How about segs_per_row from sa_table_fragmentati
on reaching 1.05 (5%) for
table fragmentation and index density from sa_index_density drops to 80%?
Thanks.

Breck Carter [TeamSybase]

2005-04-12, 7:23 am

1.05 is heading in the direction of fragmentation but it's not extreme
IMO.

80% is not particularly bad IMO. 50% is bad, *especially* if the
number of levels is 3 or (gasp!) 4.

Breck

On 11 Apr 2005 07:15:02 -0700, "mason" <masonliu@msn.com> wrote:

>What are good indications that a table and an index become too fragmented?
>How about segs_per_row from sa_table_fragmentati
on reaching 1.05 (5%) for
>table fragmentation and index density from sa_index_density drops to 80%?
>Thanks.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

mason

2005-04-12, 11:24 am

Thanks. I'll relax those thresholds a little to 1.1 and 65% as the defaults.

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:4aen51tk18he1ca
mpis345ae8dc80rqvma@
4ax.com...
> 1.05 is heading in the direction of fragmentation but it's not extreme
> IMO.
>
> 80% is not particularly bad IMO. 50% is bad, *especially* if the
> number of levels is 3 or (gasp!) 4.
>
> Breck
>
> On 11 Apr 2005 07:15:02 -0700, "mason" <masonliu@msn.com> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Rob Waywell

2005-04-14, 7:23 am

The table fragmentation value you are looking for will be dependent on the
size of the record relative to the page size. For example, if you have a
record that will normally be 5k in size and you are using a 4k page size
then you will always expect 2 fragments per record. Of course it isn't quite
that simple since any VARCHAR, LONG VARCHAR, or LONG BINARY columns greater
than 256 bytes will have the first 255 bytes stored with the record and the
balance will be stored on pages in the 'blob arena'. The pieces of columns
that are continued in the blob arena are not counted as fragments when you
count the # of fragments per record.

You may not have any tables that are wide enough to require that they be
stored across multiple pages, but I have seen many database that do have
such tables. If you do have a wide table such as that, then you will want to
ensure that you aren't constantly trying to defragment the table when it is
already as defragmented as it will get.

I agree with Breck that 50-60% is probably a better degree of index
fragmentation to care about if you are looking for an arbitrary value. I'll
also emphasize his comment on the # of levels, a sparsely populated 2 level
index is probably not a big deal but a sparsely populated 3 level index is.
If you start seeing a bunch of tables with 4 level indexes then it is likely
worth doing some benchmarking with a larger page size.
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"mason" <masonliu@msn.com> wrote in message news:425bec86$1@foru
ms-1-dub...
> Thanks. I'll relax those thresholds a little to 1.1 and 65% as the
defaults.
>
>
> "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> message news:4aen51tk18he1ca
mpis345ae8dc80rqvma@
4ax.com...
fragmented?[color=darkred]
for[color=darkred]
80%?[color=darkred]
>

mason

2005-04-14, 9:23 am

Thank you for the help. Now I have a better picture on how these items
relating to each other.

"Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
news:425e5bc2$1@foru
ms-1-dub...
> The table fragmentation value you are looking for will be dependent on the
> size of the record relative to the page size. For example, if you have a
> record that will normally be 5k in size and you are using a 4k page size
> then you will always expect 2 fragments per record. Of course it isn't
> quite
> that simple since any VARCHAR, LONG VARCHAR, or LONG BINARY columns
> greater
> than 256 bytes will have the first 255 bytes stored with the record and
> the
> balance will be stored on pages in the 'blob arena'. The pieces of columns
> that are continued in the blob arena are not counted as fragments when you
> count the # of fragments per record.
>
> You may not have any tables that are wide enough to require that they be
> stored across multiple pages, but I have seen many database that do have
> such tables. If you do have a wide table such as that, then you will want
> to
> ensure that you aren't constantly trying to defragment the table when it
> is
> already as defragmented as it will get.
>
> I agree with Breck that 50-60% is probably a better degree of index
> fragmentation to care about if you are looking for an arbitrary value.
> I'll
> also emphasize his comment on the # of levels, a sparsely populated 2
> level
> index is probably not a big deal but a sparsely populated 3 level index
> is.
> If you start seeing a bunch of tables with 4 level indexes then it is
> likely
> worth doing some benchmarking with a larger page size.
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available through the
> iAnywhere
> Developer Community at www.ianywhere.com/developer
> "mason" <masonliu@msn.com> wrote in message
> news:425bec86$1@foru
ms-1-dub...
> defaults.
> fragmented?
> for
> 80%?
>
>

Breck Carter [TeamSybase]

2005-04-15, 7:23 am

FWIW you can also get table fragmentation when the row size is much
smaller than the page size. All you have to do is fill a page with 100
byte rows, for example, then update each row to 101 bytes... voila,
every row is split. Rows are never moved by UPDATE.

Breck

On 14 Apr 2005 05:02:10 -0700, "Rob Waywell"
<rwaywell@no_spam.ianywhere.com> wrote:

>The table fragmentation value you are looking for will be dependent on the
>size of the record relative to the page size. For example, if you have a
>record that will normally be 5k in size and you are using a 4k page size
>then you will always expect 2 fragments per record. Of course it isn't quite
>that simple since any VARCHAR, LONG VARCHAR, or LONG BINARY columns greater
>than 256 bytes will have the first 255 bytes stored with the record and the
>balance will be stored on pages in the 'blob arena'. The pieces of columns
>that are continued in the blob arena are not counted as fragments when you
>count the # of fragments per record.
>
>You may not have any tables that are wide enough to require that they be
>stored across multiple pages, but I have seen many database that do have
>such tables. If you do have a wide table such as that, then you will want to
>ensure that you aren't constantly trying to defragment the table when it is
>already as defragmented as it will get.
>
>I agree with Breck that 50-60% is probably a better degree of index
>fragmentation to care about if you are looking for an arbitrary value. I'll
>also emphasize his comment on the # of levels, a sparsely populated 2 level
>index is probably not a big deal but a sparsely populated 3 level index is.
>If you start seeing a bunch of tables with 4 level indexes then it is likely
>worth doing some benchmarking with a larger page size.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links