You are not logged in or registered. Please login or register to use the full functionality of this Website SybaseTeam.Com...
Hello There, Guest! (LoginRegister) Remember Me? Current time: 04-20-2014, 01:39 PM
   Home  |  About Us  |  Sybase Training  |  Synergy  |  Consulting  |  Job Openings  |  Tech Videos  |  Rules and Disclaimer  |  Search
Post Reply 
Forum Tools
How to compute database fragmentation in Sybase
11-25-2009, 03:35 PM
Post: #1
Quote this message in a reply
How to compute database fragmentation in Sybase


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.


Find all posts by this user
12-21-2010, 08:54 PM
Post: #2
Quote this message in a reply
RE: How to compute database fragmentation in Sybase


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



Ashirvad to my Shishyas, Cheers to the others
Derek Asirvadem
Information Architect / Sr Sybase DBA
Website
Selection of Useful Documents for the Sybase DBA
Visit this user's website Find all posts by this user
12-22-2010, 01:38 AM
Post: #3
Quote this message in a reply
RE: How to compute database fragmentation in Sybase


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.



Regards,
AnVa
TechSupport-SeniorMember
Sybase Forum : Sybase Blog : Sybase Wiki
Synergy Project (SybaseTeam.Com)
Find all posts by this user
Post Reply 


Possibly Related Threads...
Thread: Author Replies: Views: Last Post
  what is tempdb database in Sybase ASE Joshi 0 2,556 10-07-2009 05:29 PM
Last Post: Joshi
  purpose of dbccdb database in sybase ase Joshi 0 2,828 10-07-2009 05:17 PM
Last Post: Joshi
  what is abort tran on log full database option in sybase Joshi 0 6,866 10-07-2009 05:14 PM
Last Post: Joshi

Options:
Forum Jump:


Contact Us | SybaseTeam | Disclaimer & Rules | Return to Top | Return to Content | Lite (Archive) Mode | RSS Syndication