This is part 7 of an 8 part series of articles about speeding up access to a SQL Server database. This series is based on chapter 8 "Speeding up Database Access"
of my book ASP.NET
Site Performance Secrets, available at amazon.com and other book sites.
If you like this article, please vote for it.
In Part 2, we looked at what fragmentation is and how to pinpoint excessive fragmentation. In this Part 7,
we'll look at fixing excessive fragmentation.
SQL Server provides two options to defragment tables and indexes, rebuild and reorganize. Here we'll examine their advantages and disadvantages.
Index Rebuild
Rebuilding an index is the most effective way to defragment an index or table. To do a rebuild, use the command:
ALTER INDEX myindex ON mytable REBUILD
This rebuilds the index physically, using fresh pages, to reduce fragmentation to a minimum.
If you rebuild a clustered index, that has the effect of rebuilding the underlying table, because the table effectively is part of the clustered index.
To rebuild all indexes on a table, use the command:
ALTER INDEX ALL ON mytable REBUILD
Index rebuilding has the disadvantage that it blocks all queries trying to access the table and its indexes. It can also be blocked by queries that already have access.
You can reduce this with the ONLINE option:
ALTER INDEX myindex ON mytable REBUILD WITH (ONLINE=ON)
This will cause the rebuild to take longer though.
Another issue is that rebuilding is an atomic operation. If it is stopped before completion, all defragmentation work done so far is lost.
Index Reorganize
Unlike index rebuilding, index reorganizing doesn't block the table and its indexes, and if it is stopped before completion, the work done so far isn't lost.
However, this comes at the price of reduced effectiveness. If an index is between 20% and 40% fragmented, reorganizing the index should suffice.
To reorganize an index, use the command:
ALTER INDEX myindex ON mytable REORGANIZE
Use the LOB_COMPACTION option to consolidate columns with Large Object data (LOB), such as image, text, ntext, varchar(max),
nvarchar(max), varbinary(max), and xml:
ALTER INDEX myindex ON mytable REORGANIZE WITH (LOB_COMPACTION=ON)
Index reorganizing is much more geared towards being performed in a busy system than index rebuilding. It is non atomic, so if it fails not all defragmentation work is lost.
It requests small numbers of locks for short periods while it executes, rather than blocking entire tables and their indexes.
If it finds that a page is being used, it simply skips that page without trying again.
The disadvantage of index reorganization is that it is less effective, because of the skipped pages, and because it won't create new pages to arrive at a better
physical organization of the table or index.
Heap Table Defragmentation
A heap table is a table without a clustered index. Because it doesn't have a clustered index, it cannot be defragmented
with ALTER INDEX REBUILD or ALTER INDEX REORGANIZE.
Fragmentation in heap tables tends to be less of a problem, because records in the table are not ordered. When inserting a record, SQL Server checks whether there
is space within the table, and if so, inserts the record there. If you only ever insert records, and not update or delete records, all records are written at the end of the table.
If you update or delete records, you may still wind up with gaps in the heap table.
Since heap table defragmentation is not normally an issue, it is not discussed in this book. Here are a few options though:
- Create a clustered index and then drop it.
- Insert data from the heap table into a new table.
- Export the data, truncate the table, and import the data back into the table.
Conclusion
In this part, we saw how to reduce fragmentation, by rebuilding or reorganizing indexes.
In the next part, we'll see how to fix hardware issues - related to memory, disks, and CPU.
