What does defragmentation do for SQL systems?

Q:

What does defragmentation do for SQL systems?

A:

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.

Have a question? Ask us here.

View all questions from Kaushik Pal.

Share this:
Written by Kaushik Pal
Profile Picture of Kaushik Pal

Kaushik is a technical architect and software consultant, having over 20 years of experience in software analysis, development, architecture, design, testing and training industry. He has an interest in new technology and innovation areas. He focuses on web architecture, web technologies, Java/J2EE, open source, WebRTC, big data and semantic technologies. Kaushik is also the founder of TechAlpine, a technology blog/consultancy firm based in Kolkata. The team at TechAlpine works for different clients in India and abroad. The team has expertise in Java/J2EE/open source/web/WebRTC/Hadoop/big data technologies and technical writing.

 Full Bio

Related Tags