SelecTViews
With Stephen Wynkoop
Meredith Ryan Smith on the show today, plus the latest news for the database world. Also, what do fingers have to do with injection and SQL Server? Find out on the show…
[Watch the Show]
How to Remove Index Fragmentation
Index fragmentation occurs when the contents of an index are not contiguous as it is stored physically. Imagine trying to read a book where the pages were not in numerical order. You may start on page one…then have to turn a few pages to find page two, and so on. Then imagine having the contents of another book shuffled into the middle of your book. This is the look of indexes after data has been modified over time.
Maintaining peek performance requires the indexes be rebuilt, so each page follows the previous page as it is stored. This process is like taking the mixed up book, separating each page, putting them in order for the separate books, and then binding each book again. The books are now more efficient to read.
In SQL Server you have a number of options for re-organizing your indexes. The options vary with the version of SQL Server you are using. With SQL Server 2008 you can Rebuild or Reorganize your indexes.
Using SQL Server 2008 Enterprise or Data Center editions, you can rebuild one or more indexes on a table as a background task. While the indexes are being rebuilt, the original indexes remain in place. Once new indexes are complete, the original indexes are replaced in a single step. This method reduces blocking of processes dependent on indexes.
Rebuilding indexes completely re-creates an index from scratch the same as dropping and re-creating that index.
Reorganization of an index is available in versions of SQL Server 2008 supporting indexes. Reorganization of indexes differs from Rebuilding indexes in that it only modifies the leaf most nodes of an index. Those are the nodes of a clustered index containing data, or the nodes of a non-clustered index, pointing to the data.
Reorganization of an index requires much less overhead, and may often be performed during peak usage with less impact to index dependent processes. It does not have the same benefit as rebuilding an index. Often, the benefit of reorganization is adequate for the current need, and it should be considered.
All database engines benefit from index defragmentation. Be sure to review the documentation for your specific engine to determine an index maintenance strategy that is right for you.
Any comments or suggestions you may have for our newsletter are greatly appreciated. Send your comments to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
A Basic Introduction to Coding DB2 SQL for Performance (Part 5)
This article series is intended to give the basics of good SQL programming to application developers. Understanding the basics of SQL coding for performance will give your enterprise applications an immediate performance boost.
Featured White Paper(s)
Enterprise Content Management
Written by AvePoint
The goal of an enterprise content management (ECM) system… (read more)