Sybase ASE 15 “reorg rebuild” fixes performance issues – why and how to tell when it’s needed

sybase

I had a table of several million rows in a Sybase ASE 15 server which was running extremely slow, even basic queries would take 5 minutes to execute, for example:

SELECT COUNT(1) FROM TABLENAME;

After googling the issue I discovered that running a reorg rebuild on the table fixed the issue.

Is there a way to determine when a table requires a reorg rebuild, or when running reorg rebuild would result in performance gains?

Should reorg rebuilds be performed on a regular basis? If so, under what circumstances?

What is the underlying cause of the degradation that a reorg rebuild fixes? Can it be avoided?

Best Answer

When a table is first indexed, especially with a clustered index, the records are in order, and there are a minimal number of index pages to find each record. As update, insert and deletes happen, the number of index pages increases. Over time this will impact query performance. It is recommended that dba's run reorgs periodically to collect garbage, compact the database, and compact the index pages. The downside to reorgs, is that prior to 15.7, they block access to the table, so typically must be run during off hours.

1 - What is the underlying cause?

Insert/Update/Delete activity in the database creates additional database pages, and index pages. The more pages the query has to read to get to the data it's looking for, the longer the query will take.

2 - Should reogs be performed on a regular basis?

Probably. Static, or rarely changing tables will need it infrequently, but as the activity on a table increases, the need for reorgs increases as well.

How should I determine when to run?

This depends on the environement you are in. The need for reorgs is directly related to the Insert/Update/Delete activity of the table. You can use the optdiag command to check the health of your tables, take a close look at some of the following values: Index page cluster ratio; Data page cluster ratio; Empty data page count; Empty leaf pages; Deleted row count; Forwarded row count; Index empty leaf page count.

Check the Sybase documentation on optidag and the Sybase documentation on reorg to get a good idea of how to determine when to run reorgs in your environment.