Speeding up database access - part 7 Fixing fragmentation

by Matt Perdeck 1. December 2011 20:13

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.

  • Part 1 Pinpointing missing indexes and expensive queries
  • Part 2 Pinpointing other bottlenecks
  • Part 3 Fixing missing indexes
  • Part 4 Fixing expensive queries
  • Part 5 Fixing locking issues
  • Part 6 Fixing execution plan reuse
  • Part 7 Fixing fragmentation
  • Part 8 Fixing memory, disk and CPU issues

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:


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:


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:


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:


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.


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.

Add comment

  Country flag

  • Comment
  • Preview


Book: ASP.NET Site Performance Secrets

ASP.NET Site Performance Secrets

By Matt Perdeck

Details and Purchase

About Matt Perdeck

Matt Perdeck PresentingMatt has written extensively on .Net and client side software development.

more >>