Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Feb 22 '13 at 14:36

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.