Hello all:
I need some help with what the Reorganize data and index pages with change free space per page percentage to 10% does.
Scenario: I have a replicated database using transactional replication with another server. Whenever this part of the maintenance plan runs, our application malfunctions until this part of the SQL maintenance plan completes. When finished, our application runs just fine...
After disabling this portion of the Database Maintenance Plan, all issues went away.
SQL Version: SQL2000 Standard Edition Service Pack 4
Servers: Dual Xeon 3.0 GHz hyper threaded with 4GB of RAM in each server.
I originally thought that this basically did a re-index or defrag/re-index of our database. Until looking at the job step, I found I was wrong, it does a -RebldIdx 10, I assume a rebuild of the indexes.
Questions:
Does this procedure lock tables in the database preventing access to them, thus causing lousy performance in our application as well as application failures?
Does this cause massive database changes which will be replicated to the secondary server or this independent of replication? Reason for asking, I do not recall seeing this behavior on some of our other systems that are on servers that do not replicate.
What is the difference between the rebuilding of indexes and just re-indexing?
Is this important to run against a database to keep it "streamlined" and if so how often should I run this?
Any help, comments, or opinions will be most appreciated.
Thank you - JPD.
JPD,
The tables are locked sequentially in the order they appear in the object list for the database. Each index for that table then has its index(s) rebuilt completely. During this time, the data in the underlying table cannot be accessed by another process.
DBCC DBREINDEX is what is used by the jobs created by the Maintenance Plan Wizard within Enterprise Manager. The maintenance plan actually calls the sqlmaint utility which then issues the TSQL. The -Rebldidx sqlmaint parameter within the maintenance job tells sqlmaint to run DBCC DBREINDEX command against all of the tables in the database, one at a time at which time all of the indexes for each table are rebuilt with fill factor inverse of the amount specified. -rebldix 10 is the same as specifying fill factor of 90%. If no amount is specified, then the value used when the index was created will be used.
Please note that while a tables indexes are being rebuilt by DBCC DBREINDEX (our implementation jobs and maintenance plan jobs) the table is LOCKED. This will cause errors and delays in the application component waiting on data from that table or a view referencing that table. The information below explains this and is cut and paste from Microsoft TechNet (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx).
RB
DBCC DBREINDEX
DBCC DBREINDEX can be used to rebuild one or more indexes for a specific table. DBCC DBREINDEX is an offline operation. While this operation is running, the underlying table is unavailable to users of the database. DBCC DBREINDEX rebuilds indexes dynamically. You do not have to know anything about the underlying table structure, nor any PRIMARY KEY or UNIQUE constraints; these are preserved automatically during the rebuild. DBCC DBREINDEX completely rebuilds the indexes, so it restores the page density levels to the original fill factor (default); or you can choose another target value for the page density. Internally, running DBCC DBREINDEX is very similar to using Transact-SQL statements to drop and re-create the indexes manually.
All work done by DBCC DBREINDEX occurs as a single, atomic transaction. The new indexes must be completely built and in place before the old index pages are released. Performing the rebuild requires adequate free space in the data file(s). With not enough free space in the data file(s), DBCC DBREINDEX may be unable to rebuild the indexes, or the indexes may be rebuilt with logical fragmentation values above zero. The amount of free space needed varies and is dependent on the number of indexes being created in the transaction. For clustered indexes, a good guideline is: Required free space = 1.2 * (average rowsize) * (number of rows).
DBCC INDEXDEFRAG
DBCC INDEXDEFRAG allows you to rebuild a specific index. Similar to using DBCC DBREINDEX, you do not have to know about the underlying table structure; however, with DBCC INDEXDEFRAG you cannot rebuild all indexes with a single statement. You must run DBCC INDEXDEFRAG once for each index you want to defragment.
Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG is an online operation; therefore, the table and indexes are available while the index is being defragmented. Another major difference is that DBCC INDEXDEFRAG can be stopped and restarted without losing any work. The entire DBCC DBREINDEX operation runs as one atomic transaction. This means if you stop DBCC DBREINDEX the entire operation is rolled back, and you must start over. However, if you stop DBCC INDEXDEFRAG it stops instantly and no work is lost, because each unit of work performed by DBCC INDEXDEFRAG occurs as a separate transaction.
DBCC INDEXDEFRAG consists of two phases:
1. Compact the pages and attempt to adjust the page density to the fillfactor that was specified when the index was created. DBCC INDEXDEFRAG attempts to raise the page-density level of pages to the original fillfactor. DBCC INDEXDEFRAG does not, however, reduce page density levels on pages that currently have a higher page density than the original fillfactor.
2. Defragment the index by shuffling the pages so that the physical ordering matches the logical ordering of the leaf nodes of the index. This is performed as a series of small discrete transactions; therefore, the work done by DBCC INDEXDEFRAG has a small impact to overall system performance. Figure 8 shows the page movements performed during the defragmentation phase of DBCC INDEXDEFRAG.
Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG
One last consideration is the difference in the amount of data that is written to the transaction log when using DBCC INDEXDEFRAG and DBCC DBREINDEX. The amount of information logged by DBCC INDEXDEFRAG depends on the level of fragmentation and the amount of work that is performed. The tests resulted in DBCC INDEXDEFRAG logging significantly less than DBCC DBREINDEX when the database was in full recovery mode. The amount of data logged by DBCC INDEXDEFRAG, however, can vary greatly. This is because the amount of work that DBCC INDEXDEFRAG performs when defragmenting indexes is dependent on the number of page movements and the amount of page compaction necessary. You can reclaim the log space used by DBCC INDEXDEFRAG by backing up the log because the work performed is a series of small transactions.
With respect to log usage, DBCC DBREINDEX behaves slightly differently from DBCC INDEXDEFRAG, with the biggest difference being the amount of log used when in bulk logged recovery mode. When in full recovery mode, DBCC DBREINDEX logs images of each index page, which does not occur when in bulk logged mode. For this reason, in full recovery mode, the log space required by DBCC DBREINDEX is roughly equivalent to the number of index pages multiplied by 8 KB. You can use DBCC SHOWCONTIG to determine the number of pages in a given index. For large-scale environments, consider changing the recovery mode to bulk logged when you run DBCC DBREINDEX. Then return to full recovery mode after index maintenance has been performed.
No comments:
Post a Comment