How to compute database fragmentation in Sybase

[albert]

Command

dbcc traceon(3604)
go
dbcc tab(production, my_table, 0)
go

Interpretation

A delta of one means the next page is on the same track, two is a short seek, three is a long seek. You can play with these constants but they aren't that important.

A table I thought was unfragmented had L1 = 1.2 L2 = 1.8

A table I thought was fragmented had L1 = 2.4 L2 = 6.6
How to Fix

You fix a fragmented table with clustered index by dropping and creating the index. This measurement isn't the correct one for tables without clustered indexes. If your table doesn't have a clustered index, create a dummy one and drop it.

[DerekAsirvadem]

(11-25-2009 03:35 PM)albert Wrote:  Command

dbcc tab(production, my_table, 0)

I would say the DERIVED_STAT() function is much easier:

SELECT DEREIVED_STAT ( object [, index [, partition ] ] , "{ SPUT | LIO | DPCR | IPCR | DRCR } ")

Details in the Blocks manual. Either ibject names or ids can be used.

Quote:How to Fix

You fix a fragmented table with clustered index by dropping and creating the index. This measurement isn't the correct one for tables without clustered indexes. If your table doesn't have a clustered index, create a dummy one and drop it.

That will correct the fragmentation of the table within itself, at the object level only; create a new PageChain, etc. I call that Level II. The DERIVED_STAT() will now show a contiguous object, and Asynchronous Pre Fetch may now be possible. Large I/O is still lost.

For DPL/DRL tables, it will correct the peculiar fragmentation that only those tables have, at least until INSERT/UPDATE/DELETE have their effect. I call that Level III.

But it will not correct the fragmentation at the database level; the Allocation Units, and the fragmentation within the AllocationUnits. I call that Level III. The DERIVED_STAT() do not show this fragmentation. Correctly it reclaims Large I/O.

[sybanva]

I would like to add, DEREIVED_STAT is available in 15.x version.

(12-21-2010 08:54 PM)DerekAsirvadem Wrote:  

(11-25-2009 03:35 PM)albert Wrote:  Command

dbcc tab(production, my_table, 0)

I would say the DERIVED_STAT() function is much easier:

SELECT DEREIVED_STAT ( object [, index [, partition ] ] , "{ SPUT | LIO | DPCR | IPCR | DRCR } ")

Details in the Blocks manual. Either ibject names or ids can be used.

Quote:How to Fix

You fix a fragmented table with clustered index by dropping and creating the index. This measurement isn't the correct one for tables without clustered indexes. If your table doesn't have a clustered index, create a dummy one and drop it.

That will correct the fragmentation of the table within itself, at the object level only; create a new PageChain, etc. I call that Level II. The DERIVED_STAT() will now show a contiguous object, and Asynchronous Pre Fetch may now be possible. Large I/O is still lost.

For DPL/DRL tables, it will correct the peculiar fragmentation that only those tables have, at least until INSERT/UPDATE/DELETE have their effect. I call that Level III.

But it will not correct the fragmentation at the database level; the Allocation Units, and the fragmentation within the AllocationUnits. I call that Level III. The DERIVED_STAT() do not show this fragmentation. Correctly it reclaims Large I/O.