Click here to Skip to main content
15,666,033 members
Articles / Database Development / SQL Server
Posted 6 Dec 2011


28 bookmarked

Speeding up database access - Part 7: Fixing fragmentation

Rate me:
Please Sign up or sign in to vote.
4.41/5 (8 votes)
20 Dec 2011CPOL3 min read
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.

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 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:


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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Australia Australia
Twitter: @MattPerdeck
Current project: JSNLog JavaScript Logging Package

Matt has over 9 years .NET and SQL Server development experience. Before getting into .Net, he worked on a number of systems, ranging from the largest ATM network in The Netherlands to embedded software in advanced Wide Area Networks and the largest ticketing web site in Australia. He has lived and worked in Australia, The Netherlands, Slovakia and Thailand.

He is the author of the book ASP.NET Performance Secrets ( in which he shows in clear and practical terms how to quickly find the biggest bottlenecks holding back the performance of your web site, and how to then remove those bottlenecks. The book deals with all environments affecting a web site - the web server, the database server and the browser.

Matt currently lives in Sydney, Australia. He recently worked at Readify and the global professional services company PwC. He now works at SP Health, a global provider of weight loss web sites such at CSIRO's and

Comments and Discussions

-- There are no messages in this forum --