sybase delete records gain space back?

telliot79
telliot79 used Ask the Experts™
on
experts,

quick question - once I've deleted records from a table in my sybase database, do I need to rebuild the clustered index to reclaim that space, or is it done automagically?

Thanks in advance for the help,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Principal Consultant
Most Valuable Expert 2012
Commented:
The answer is "it depends".

If these tables are locked "allpages" (usually the default), free space on any page is compacted and rows moved up on the page. Any data pages that are completely deleted of all rows are no longer used and thus this page is now "free space" in that it can be used for other rows for this table.

It can't necessarily be used for other tables or objects though, because the minimum allocation in Sybase ASE is one "extent" which is eight contiguous pages. If there are still other pages allocated in this extent, any free pages in the extent are available for space for this object, but not for any other. You would have to have deleted all rows on all eight pages in an extent before that extent can be used for other objects.

If your table is locked "datapages" or "datarows" then it's a bit more complicated. Rows are deleted "as is" and other rows on the page are not "moved up" to compact that space. This "non-contiguous free space" is fragmentation and chews up space. It is slowly cleaned up over time by the background housekeeper process.

In all cases you can check the "wasted space" by running the free & built-in tool "optdiag" which is run from the O/S command line. In particular look for the "Data Page Cluster Ratio", which is a numeric between 0 and 1. The higher the better: 1.0 implies perfect 100% packing (not actually a desirable state in most cases). If you see the number is below 0.9 it's time to start thinking about some kind of defragmentation. I personally think 0.8 is a bit of an alarm bell (1 in 5 pages is wasted space) but the exact answer depends on each environment.

As you say, drop & recreate clustered index is one way to do this. Another is to bcp out every row, truncate the table, and bcp them back in again. For your DOL (Data-Only Locked tables, or datapages and datarows locking), you also have the "reorg" command which comes in various flavours which are tradeoffs between time/impact and space/effectiveness. (In ASE 15+ you can also run reorg on "allpages" tables.)

TL;DR: After a while, yes, you do need to think about some kind of defragmentation strategy. Many sites don't bother, although I think they're hurting themselves with that.

Author

Commented:
thanks Joe_Woodhouse. excellent answer as always.
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

Commented:
Thanks. :)

I also forgot to mention that in ASE 12.5.3 onwards, you can also get the Data Page Cluster Ratio directly within ASE using the "derived_stat" function. It's written up in the manuals, and the number works exactly the same way - your goal is to keep it close to (say) the 0.95 mark (implying 5% space on pages to adsorb changes in variable row lengths).

Author

Commented:
brilliant thx. i'll look into it right away.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial