Question

What does defragmentation do for SQL systems?

Answer

Continuous database maintenance and monitoring are the key elements to be considered for running a SQL system smoothly. When a database is created and populated, initially the data is placed in a contiguous physical location (if sufficient contiguous physical space is available). So, in this case the logical ordering and physical ordering of data are likely to be similar, and it increases performance.

When the data is modified, deleted or updated, relevant indexes are also updated automatically to reflect those changes. As a result, indexes become fragmented and the information scattered across the storage space. It changes the physical ordering of data (as it loses contiguous allocation) and the retrieval becomes time consuming, resulting in slow database performance.

The solution to this problem is to perform defragmentation on a periodic basis. Defragmentation actually rebuilds or reorganizes the indexes to match the logical ordering of data with the physical ordering. Before performing any defragmentation operation, all the indexes should be analyzed properly. The analysis results determine whether a reorganization or rebuild is required.

The two main operations performed by the defragmentation process are:

  • Index reorganization – Index reorganization is performed when the fragmentation is at a low level and performance is not severely affected. This process actually does the physical reordering of leaf-level pages to match the logical ordering. It does not create any new pages; it only reorders the existing pages. Reorganization can be performed while the system is online without blocking normal database operations.
  • Index rebuilding – The index rebuilding process is performed when the fragmentation is at a deeper level and performance is too slow. In this process, the original index is dropped and a fresh new index is built. So the physical and logical ordering are set back to the original positions and the performance improves manifold. Rebuilding can also create new pages as required, and it can be performed in offline or online mode.

Therefore, defragmentation should be a part of the SQL Server maintenance process and needs to be taken seriously. A proper query analysis plan has to be built and followed. Based on the query analysis output, rebuilding or reorganization of indexes must be done. In short, defragmentation is essential for performance improvement of SQL systems.

Related Terms

Justin Stoltzfus

Justin Stoltzfus is an independent blogger and business consultant assisting a range of businesses in developing media solutions for new campaigns and ongoing operations. He is a graduate of James Madison University.Stoltzfus spent several years as a staffer at the Intelligencer Journal in Lancaster, Penn., before the merger of the city’s two daily newspapers in 2007. He also reported for the twin weekly newspapers in the area, the Ephrata Review and the Lititz Record.More recently, he has cultivated connections with various companies as an independent consultant, writer and trainer, collecting bylines in print and Web publications, and establishing a reputation…