Click here to Skip to main content
Click here to Skip to main content

Speeding up database access - Part 7: Fixing fragmentation

, 20 Dec 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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 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.

License

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

Share

About the Author

Matt Perdeck
Web Developer
Australia Australia
Twitter: @MattPerdeck
Blog: mattperdeck.com
Current project: JSNLog JavaScript Logging Package
 
Matt has over 6 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 recently wrote a book, ASP.NET Performance Secrets (www.packtpub.com/asp-net-site-performance-secrets/book) 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. After 2 years at Readify, he now works at the global professional services company PwC. His current contract ends at 29 June 2014.
Follow on   Twitter   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 20 Dec 2011
Article Copyright 2011 by Matt Perdeck
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid