Effective Database Maintenance and Management – Index Fragmentation

We have said before that indexes are easily the most important database structures insofar as database performance tuning is concerned. Therefore, you must pay keen attention to their configuration, structure and arrangement in order to get the best level of performance out of them.

Theory of index fragmentation

Fragmentation does not only occur at the file-system level and inside log files; data files can also be affected by fragmentation – specifically within the sections that store index and table data. This can happen in two ways:

• Internal fragmentation – occurs within individual index and data pages
• Scan fragmentation – within index (logical scan fragmentation) or table (extent scan fragmentation) structures that constitute pages.

Internal fragmentation

This occurs where a page contains a lot of empty space, which also comes about where a single table or index record occupies more than half of the size of a page. This would mean that only one record stores in the page. Where this is the problem, only a scheme change would correct problem but more often than not even that is ineffective.

The more common reason for internal fragmentation is data modifications that leave empty spaces on pages e.g. updates, deletions and inserts. A mis-configured fill-factor can also cause fragmentation. Depending on the schema of the table or index, free space created may be irrecoverable, meaning that over time the amount of empty unusable space will keep growing in the database.

Wasted space implies that more data or index pages will be necessary to store the same amount of data. This takes up more disk space, and forces a query to have to issue more IOs to read the same volume of data.

Logical and extent scan fragmentation

This occurs due to an operation referred to as a page split – when a record defined for insertion on a particular index page that does not have enough space to fit the record. As a result, the page will split into two, and the latter portion moved into a new page that is usually detached from the old page. The data therefore becomes fragmented.

The concept similarly applies to extent scan fragmentation. The fragmentation interferes with the SQL Server’s ability to carry out scans efficiently, whether it is throughout the index or table, or restricted by a WHERE clause in a query.

Solutions to fragmentation

Changing the schema of a table or index is very hard, even impossible, but it is the best way to prevent fragmentation. Where prevention is not available, removal of fragmentation can be by rebuilding and reorganizing indexes. Rebuilding means creation of a new copy of the index, one that is contiguous and compact and then discarding the fragmented one. Rebuilding should be offline, though recent versions of SQL servers allow online rebuilding, with restrictions.

Reorganizing is an easier operation, one that can take place online and a space-efficient alternative to rebuilding. Reorganization is through using an in-place algorithm to defragment and compact an index. Details of trade-offs between the two can be accessed through online resources in order to determine the best method for your own database. Further assistance is available at the remote DBA expert at remotedba.com.

Some DBAs choose to rebuild or reorganize all indexes at the end of the day or week as opposed to finding out which indexes have fragments. Developers usually implement this as part of a maintenance plan option, but it is a very poor choice where databases are larger and where resources come at a premium.

Whichever method is applied, index fragmentation should undergo regularly investigation and corrected for better database performance.

Bio : Charlie Brown is a free lancer content writer. He has written many good and informative articles on Technology, software, internet etc. By this article, he has given information on Effective Database Maintenance. In his free time, he loves to collect more and more information on different Topics of Technology.