| |
 |
|
SQL Server Tips by Robin Schumacher
|
Removing Fragmentation
What should be done when fragmentation does indeed exist in one or
more of the objects? The standard prescription is to perform a
reorganization; however, not all reorganization methods are created
equal. There are four main approaches in SQL Server 7 and 2000 that
can be taken to perform reorganization. Each approach has its
positive and negative points:
-
Drop/Create Index: This is the most
basic way to reorganize indexes; however, this option cannot be
used if the index supports a constraint. Plus, it is not the
best thing to do during normal business operations, as the index
is naturally unavailable for use during construction. Worse yet,
for clustered index creations, the entire table is unavailable
for use (read or write) and for non-clustered index creations,
only read operations are allowed on the parent table.
-
Create Index with drop_existing: This
option gets the DBA around the problem of not being able to
re-create an index that supports a constraint; however, all
other headaches identified above with the drop/create index
method still apply.
-
DBCC DBREINDEX: This method allows the
DBA to rebuild one or more indexes without having to know their
Data Definition Language (DDL) definition. This command can also
be safely issued if the indexes support constraints on a table.
Other advantages include the rebuilding of statistics during the
operation and the fact that the DBCC command can make use of
multi-processor environments and run quite fast in such
settings. Drawbacks to this method include the fact that it is
also an offline operation and can negate object use during the
utility run. It is also an atomic transaction, so if it is
stopped before completion, all defragmentation work is lost.
-
DBCC INDEXDEFRAG: Available for SQL
Server 2000 and higher, this method has the distinct advantage
of being an online operation, meaning the DBA can reorganize
while the objects stay available for use. Unlike DBCC DBREINDEX,
this method can be stopped without any of the previous
defragmentation work being lost. Of course, there has to be some
penalty to pay for all the positives associated with this
method. The way DBCC INDEXDEFRAG manages to be an online
operation is that it skips over pages that are in use during its
run, meaning that it is possible to miss some fragmentation.
Also, DBCC INDEXDEFRAG is not as fast as DBCC DBREINDEX because
of its inability to use multiple processors for its work, and it
doesn’t rebuild object statistics. This operation can also make
extent scan fragmentation worse. It eliminates extents but does
not improve the physical order of the remaining extents.
Tables are not mentioned at all in the above
reorganization methods. It is rare to have to rebuild standard heap
tables, tables without clustered indexes. Fragmentation matters
little in heap tables because the data rows do not have to be kept
in order. Further, the SQL Server does a good job of keeping the
data pages full because it will reuse space left empty from DELETEs
and such. The main space headache facing most heap tables is row
forwarding. In the special case in which a heap table is exhibiting
signs of fragmentation, a clustered index on the table, with a high
fillfactor, can be created and the index dropped after completion.
The other option is to copy all the data out of the table, issue a
TRUNCATE or drop/recreate the table and copy all the data back in.
In SQL Server 2005, all the previously mentioned options are
available, but there are also the two following additions:
-
ALTER INDEX REORGANIZE: This option
reorders the leaf level pages of the index in logical order. The
good news is that this operation is online, so concurrency
issues will not be encountered. The down side is that it is not
as complete as a drop/create statement. This option replaces the
DBCC INDEXREFRAG command.
-
ALTER INDEX REBUILD: This option
replaces DBCC DBREINDEX, but is online in nature unless the
index is eXtensible Markup Language (XML). It is also possible
to rebuild just one or more partitions of an index instead of
the entire index.
In SQL Server 2005, the index can also be rebuilt online via the
create index…drop_existing statement by utilizing the new
online=on clause. An example of this approach might be:
create nonclustered index emp_ssn
on dbo.emp(emp_ssn)
with (drop_existing=on,online=on)
go
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
