What does defragmentation do for SQL systems?

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.