| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
System Administration Guide (Online Only) |
![[-]](/dwicons/i_colpse.gif)
| Chapter 24 Using the reorg Command |
Chapter 24
Update activity against a table can eventually lead to inefficient utilization of space and reduced performance. The reorg command reorganizes the use of table space and improves performance.
The reorg command provides four subcommands for carrying out different types and levels of reorganization:
reorg forwarded_rows undoes row forwarding.
reorg reclaim_space reclaims unused space left on a page as a result of deletions and row-shortening updates.
reorg compact both reclaims space and undoes row forwarding.
reorg rebuild undoes row forwarding and reclaims unused page space, as does reorg compact. In addition, reorg rebuild:
Rewrites all rows to accord with a table's clustered index, if it has one
Writes rows to data pages to accord with any changes made in space management settings through sp_chgattribute
Drops and re-creates all indexes belonging to the table
The reclaim_space, forwarded_rows, and compact subcommands:
Minimize interference with other activities by using multiple small transactions of brief duration. Each transaction is limited to eight pages of reorg processing.
Provide resume and time options that allow you to set a time limit on how long a reorg runs and to resume a reorg from the point at which the previous reorg stopped. This allows you to, for example, use a series of partial reorganizations at off-peak times to reorg a large table. For more information, see "resume and time options for reorganizing large tables".
The following considerations apply to the rebuild subcommand:
reorg rebuild holds an exclusive table lock for its entire duration. On a large table this may be a significant amount of time. However, reorg rebuild accomplishes everything that dropping and re-creating a clustered index does and takes less time. In addition, reorg rebuild rebuilds the table using all of the table's current space management settings. Dropping and re-creating an index does not use the space management setting for reservepagegap.
In most cases, reorg rebuild requires additional disk space equal to the size of the table it is rebuilding and its indexes.
The following restrictions hold:
The table specified in the command, if any, must use either the datarows or datapages locking scheme.
You must be a System Administrator or the object owner to issue reorg.
You cannot issue reorg within a transaction.
reorg is useful when:
A large number of forwarded rows causes extra I/O during read operations.
Inserts and serializable reads are slow because they encounter pages with noncontiguous free space that needs to be reclaimed.
Large I/O operations are slow because of low cluster ratios for data and index pages.
sp_chgattribute was used to change a space management setting (reservepagegap, fillfactor, or exp_row_size) and the change is to be applied to all existing rows and pages in a table, not just to future updates.
To assess the need for running a reorg, you can use statistics from the systabstats table and the optdiag utility. systabstats contains statistics on the utilization of table space, while optdiag generates reports based on statistics in both systabstats and the sysstatistics table.
For information on the systabstats table, see the Performance and Tuning Guide. For information about optdiag, see Utility Programs for UNIX Platforms.
Several types of activities reclaim or reorganize the use of space in a table on a page-by-page basis:
Inserts, when an insert encounters a page that would have enough room if it reclaimed unused space.
The update statistics command (for index pages only)
Re-creating clustered indexes
The housekeeper task, if enable housekeeper GC is set to 1
Each of these has limitations and may be insufficient for use on a large number of pages. For example, inserts may execute more slowly when they need to reclaim space, and may not affect many pages with space that can be reorganized. Space reclamation under the housekeeper task compacts unused space, but it runs only when no other tasks are requesting CPU time, so it may not reach every page that needs it.
If an update makes a row too long to fit on its current page, the row is forwarded to another page. A reference to the row is maintained on its original page, the row's home page, and all access to the forwarded row goes through this reference. Thus, it always takes two page accesses to get to a forwarded row. If a scan needs to read a large number of forwarded pages, the I/Os caused by extra page accesses slow performance.
reorg forwarded_rows undoes row forwarding by either moving a forwarded row back to its home page, if there is enough space, or by deleting the row and reinserting it in a new home page.
You can get statistics on the number of forwarded rows in a table by querying systabstats and using optdiag.
The syntax for reorg forwarded_rows is:
reorg forwarded_rows tablename
[with {resume, time = no_of_minutes}]For information about the resume and time options, see "resume and time options for reorganizing large tables".
reorg forwarded_rows does not apply to indexes, because indexes do not have forwarded rows.
reorg forwarded_rows uses allocation page hints to find forwarded rows. Because it does not have to search an entire table, this command executes quickly, but it may miss some forwarded rows. After running reorg forwarded_rows, you can evaluate its effectiveness by using optdiag and checking "Forwarded row count." If "Forwarded row count" is high, you can then run reorg compact, which goes through a table page by page and undoes all row forwarding.
When a task performs a delete operation or an update that shortens row length, the empty space is preserved in case the transaction is rolled back. If a table is subject to frequent deletes and row-shortening updates, unreclaimed space may accumulate to the point that it impairs performance.
reorg reclaim_space reclaims unused space left by deletes and updates. On each page that has space resulting from committed deletes or row-shortening updates, reorg reclaim_space rewrites the remaining rows contiguously, leaving all the unused space at the end of the page. If all rows have been deleted and there are no remaining rows, reorg reclaim_space deallocates the page.
You can get statistics on the number of unreclaimed row deletions in a table from the systabstats table and by using the optdiag utility. There is no direct measure of how much unused space there is as a result of row-shortening updates.
The syntax for reorg reclaim_space is:
reorg reclaim_space tablename [indexname]
[with {resume, time = no_of_minutes}]If you specify only a table name, only the table's data pages are reorganized to reclaim unused space; in other words, indexes are not affected. If you specify an index name, only the pages of the index are reorganized.
For information about the resume and time options, see "resume and time options for reorganizing large tables".
reorg compact combines the functions of reorg reclaim_space and reorg forwarded_rows. Use reorg compact when:
You do not need to rebuild an entire table (reorg rebuild); however, both row forwarding and unused space from deletes and updates may be affecting performance.
There are a large number of forwarded rows. See "Using reorg compact to remove row forwarding".
The syntax for reorg compact is:
reorg compact tablename
[with {resume, time = no_of_minutes}]For information about the resume and time options, see "resume and time options for reorganizing large tables".
Use reorg rebuild when:
Large I/O is not being selected for queries where it is usually used, and optdiag shows a low cluster ratio for datapages, data rows, or index pages.
You used sp_chgattribute to change one or more of the exp_row_size, reservepagegap, or fillfactor space management settings and you want the changes to apply not only to future data, but also to existing rows and pages. For information about sp_chgattribute, see the Reference Manual.
If a table needs to be rebuilt because of a low cluster ratio, it may also need to have its space management settings changed (see "Changing space management settings before using reorg rebuild").
reorg rebuild uses a table's current space management settings to rewrite the rows in the table according to the table's clustered index, if it has one. All indexes on the table are dropped and re-created using the current space management values for reservepagegap and fillfactor. After a rebuild, a table has no forwarded rows and no unused space from deletions or updates.
The syntax for reorg rebuild is:
reorg rebuild tablename [index_name]
Before you run reorg rebuild on a table:
Set the database option select into/bulkcopy/pllsort to true and run checkpoint in the database.
Make sure that additional disk space, equal to the size of the table and its indexes, is available.
To set select into/bulkcopy/pllsort to true and checkpoint the database, use the following isql commands:
1> use master
2> go
1> sp_dboption pubs2,
"select into/bulkcopy/pllsort", true
2> go
1> use pubs2
2> go
1> checkpoint
2> goFollowing a rebuild on a table:
You must dump the database containing the table before you can dump the transaction log.
Distribution statistics for the table are updated.
All stored procedures that reference the table will be recompiled the next time they are run.
When reorg rebuild rebuilds a table, it rewrites all table and index rows according to the table's current settings for reservepagegap, fillfactor, and exp_row_size. These properties all affect how quickly inserts cause a table to become fragmented, as measured by a low cluster ratio.
If it appears that a table quickly becomes fragmented and needs to be rebuilt too frequently, it may be a sign that you need to change the table's space management settings before you run reorg rebuild.
To change the space management settings, use sp_chgattribute (see the Reference Manual). For information on space management settings, see the Performance and Tuning Guide.
The reorg rebuild command allows you to rebuild individual indexes while the table itself is accessible for read and update activities.
The syntax for rebuilding an index is:
reorg rebuild table_name index_name
To use reorg rebuild, you must be the table owner or the Database Owner, or have System Administrator privileges.
If you omit the index name, the entire table is rebuilt.
If you specify an index, only that index is rebuilt.
Requirements for using reorg rebuild on an index are less stringent than for tables. The following rules apply:
You do not need to set select into to rebuild an index.
Rebuilding a table requires space for a complete copy of the table. Rebuilding an index works in small transactions, and deallocates pages once they are copied; therefore, the process only needs space for the pages copied on each transaction.
You can rebuild the index on a table while transaction level scans (dirty reads) are active.
The reorg command applies only to tables using datarows or datapages locking. You cannot run reorg on a table that uses allpages locking.
You cannot run reorg on a text index, the name from sysindexes associated with a text chain.
You cannot run reorg within a transaction.
You can do a dump tran on a table after rebuilding its index. However, you cannot do a dump tran if the entire table has been rebuilt.
You can rebuild the index for systabstats, but you cannot run reorg rebuild on the table itself.
Although online index rebuilding is allowed on a placement index, it rebuilds only the index pages. The data pages remain untouched, which means datarows are neither sorted nor rewritten to fresh pages. You can rebuild data pages by dropping a placement index, and then re-creating it.
Rebuilding a single index rewrites all index rows to new pages. This improves performance by:
Improving clustering of the leaf level of the index
Applying stored values for the fill factor on the index, which can reduce page splits
Applying any stored value for reservepagegap, which can help reserve pages for future splits
To reduce contention with users whose queries need to use the index, reorg rebuild locks a small number of pages at a time. Rebuilding an index is a series of independent transactions, with some independent, nested transactions. Approximately 32 pages are rebuilt in each nested transaction and approximately 256 pages are rebuilt in each outer transaction. Address locks are acquired on the pages being modified and are released at the end of the topaction. The pages deallocated in a transaction are not available for reuse until the next transaction begins.
If the reorg rebuild command stops running, the transactions that are already committed are not rolled back. Therefore, the part that has been reorganized is well clustered with desired space utilization, and the part that has not been reorganized is the same as it was before you ran the command. The index remains logically consistent.
Rebuilding the clustered index does not affect the data pages of the table. It only affects the leaf pages and higher index levels. Non-leaf pages above level 1 are not rebuilt.
If you do not specify fill_factor or reservepagegap, rebuilding an index requires additional space of approximately 256 pages or less in the data segment. The amount of log space required is larger than that required to drop the index and re-create it using create index, but it should be only a small fraction of the actual index size. The more additional free space is available, the better the index clustering will be.
reorg rebuild may not rebuild those parts of the index that are already well clustered and have the desired space utilization.
Index scans are faster after you run reorg.
Running reorg against a table can have a negative effect on performance of concurrent queries.
Running reorg rebuild indexname on a large table may take a long time. Periodic status messages are printed to give the user an idea of how reorg has progressed. Starting and ending messages are written to the error log and to the client process executing reorg. In-progress messages go only to the client.
A status reporting interval is calculated as either 10 percent of the pages to be processed or 10,000 pages, whichever is larger. When this number of pages is processed, a status message is printed. Therefore, no more than 10 messages are printed, regardless of the size of the index. Status messages for existing reorg commands are printed more frequently.
Use the resume and time options of the reorg command when reorganizing an entire table would take too long and interfere with other database activities. time allows you to run a reorg for a specified length of time. resume allows you to start a reorg at the point in a table where the previous reorg left off. In combination, the two options allow you to reorganize a large table by running a series of partial reorganizations (for example, during off-hours).
resume and time not available with reorg rebuild.
The syntax for resume and time is:
reorg reclaim_space tablename [indexname]
[with {resume, time = no_of_minutes}]reorg forwarded_rows tablename
[with {resume, time = no_of_minutes}]reorg compact tablename
[with {resume, time = no_of_minutes}]The following considerations apply:
If you specify only the resume option, the reorg begins at the point where the previous reorg stopped and continues to the end of the table.
If you specify only the time option, the reorg starts at the beginning of the table and continues for the specified number of minutes.
If you specify both options, the reorg starts at the point where the previous reorg stopped and continues for the specified number of minutes.
The no_of_minutes argument in the time option refers to elapsed time, not CPU time. For example, to run reorg compact for 30 minutes, beginning where a previous reorg compact finished, enter:
reorg compact tablename with resume, time=30
If the reorg process goes to sleep during any part of the 30 minutes, it still counts as part of the elapsed time and does not add to the duration of the reorg.
When the amount of time specified has passed, reorg saves statistics about the portion of the table or index that was processed in the systabstats table. This information is used as the restart point for a reorg with the resume option. The restart points for each of the three subcommands that take resume and time options are maintained separately. You cannot, for example, start a reorg with reorg reclaim_space and then resume it with reorg compact.
If you specify no_of_minutes, and reorg arrives at the end of a table or an index before the time is up, it returns to the beginning of the object and continues until it reaches its time limit.
resume and time allow you to reorganize an entire table or index over multiple runs. However, if there are updates between reorg runs, some pages may be processed twice and some pages may not be processed at all.
|
|