If you want to learn more about what index fragmentation involves and why it causes consternation when it occurs, read on for a lowdown of the basics.
What is SQL index fragmentation?
Databases use indexes to speed up the retrieval of information stored within larger tables. In this sense, they are very much like an index in a reference book, except they point to columns of data rather than pages of text. Fragmentation happens inevitably over time. When a database changes and grows, indexes will no longer match the order of the data they refer to.
The causes of fragmentation
As mentioned, fragmentation is unavoidable and parcel of how SQL databases function, explained concisely at https://www.sentryone.com/sql-server/sql-index-fragmentation. Fragmentation can be caused internally, such as when too much free space is present within data pages. The more free space, the more complex work indexes have to do to find the information they need. External fragmentation is also an issue caused by data pages becoming disordered after the information is added. The database has to split the record across two pages because of its size, compromising the physical continuity.
Signs that you have a fragmented index
Sluggish database performance with no other obvious reason is a catch-all option for determining that index fragmentation may have gotten out of hand. The longer processes take to complete, the higher the likelihood that fragmentation has reached problematic levels. The main thing to note is that all indexes will become fragmented as the database is used. Hence, it is a universal problem for administrators to cope with, rather than one which can be avoided altogether.
How to prevent index fragmentation
Just because fragmentation is inevitable does not mean it cannot be prevented through careful planning. First, schedule regular maintenance when indexes can be rebuilt to undo the fragmentation over time. Be sure that the maintenance does not conflict with any peak periods of usage because otherwise, this could further hamper the experience for end-users. Next, make use of the software tools that are at your disposal to monitor and deal with fragmentation on time, specifically if it gets out of hand before your scheduled sessions of defragmentation. SQL Server itself has a fragmentation analysis tool when viewing the properties of a given index. Still, it may be more beneficial to leverage a third-party monitoring platform to automate this and provide alerts to inform your next steps on the fly.
Conclusion
Index fragmentation cannot be taken for granted if you are responsible for running an SQL database. A little bit of fragmentation is fine but left unchecked. It will run riot and reduce performance to a crawl. Small, regular bouts of maintenance combined with monitoring will put you on the right track.
