Ways of Managing SQL Server Database Fragmentation

If you are an SQL admin or user, you must be familiar with the term” fragmentation.” In every SQL Server, indexes get fragmented as the database usage is increased. Fragmentation within a limit is harmless, but as the fragmentation percentage increases, it affects the database efficiency. Keeping this situation in mind, this post will discuss methods of managing SQL Server database fragmentation. We will also talk about other matters related to SQL Server database fragmentation.

What is Fragmentation and Its Categories

SQL database indexes help your database to perform properly. Whenever an SQL Server is used for a query, view, or data retrieval, indexes are being used one way or another. Especially in case of lengthy database operations, support from properly managed indexes is mandatory for correct and fast result. Along with the database usage growth, page split keeps occurring and at the same time those page indexes keep fragmenting. That is how fragmentation occurs in SQL Server. There are two types of index fragmentation: internal and external. External fragmentation means the logical page order is not similar to physical page order. Internal fragmentation happens when there is too much white space on the pages.

How Fragmentation Affects SQL Server Performance

Fragmentation forces database to go through multiple index entries to process a query. As a result, it takes more time to use SQL Server to execute any query. The situation gets worse when you need to fetch data from a read-only table quickly, but in case of dynamic table without any page split it does not matter much.

What is the Desired Level of SQL Server Database Fragmentation

So, we can see that the effect of index fragmentation depends on index usage and category of table that is being processed. For this reason, there is no fixed SQL Server database index fragmentation value that can be considered universally standard. Users need to consider their specific requirement and manage database fragmentation.

Learn to Manage SQL Server Database Fragmentation

This process of fragmentation is impossible to avoid, so the only option left is to manage index fragmentation properly. Considering the importance of this process, most of the SQL experts consider it as a regular task. In this segment, we will learn how to manage the fragmentation in SQL Database.

Detecting Fragmentation

For adopting any measure for fragmentation management, the level of fragmentation has to be determined. In order to analyze fragmentation, sys.dm_db_index_physical_stats” DMS is commonly used. This function will get you a result set containing the fragmentation level. Some important columns of the result set are:

• avg_page_space_used_in_percent: This column shows how much database space is used by the pages. The value is shown in percentage and indicates internal index fragmentation.
• avg_fragmentation_in_percent: In this column, external index fragmentation percentage can be viewed. For Heap Tables and Tables with Clustered Indexes, this column shows different values.
• fragment_count: This column displays continuous data fragments in the index.

Managing SQL Server Index Fragmentation

If you want to keep the index fragmentation rate in control, make sure to apply these measures for your SQL Server database.

• Apply index fill factors settings
• Avoid adding records using random key values
• Select cluster key according to the Insert pattern of the table
• Stop making records longer by updating them
• Avoid updating key columns of the index

How to Apply Fill Factor Settings

The most useful technique to manage the Index fragmentation is to set Fill Factor settings. In this parameter, users can set the data page fulfillment percentage reaching which only, an index will be created. The value has to be entered between 1 to 100 where 100 means an index will be made when a page is 100% full. Remember that Fill Factor is only used during the creation, rebuilding, or reorganization of an index. If you want to set instance Fill Factor you can use sp_configure , but it is not recommended.

What If My Index Fragment Rate is High

As mentioned earlier, there is no fixed standard for index fragment rate. But there are some common practices among the SQL users if their index fragmentation percentage reaches a certain level. If the SQL database fragmentation rate is 0 to 5-10% you do not have to do anything. If it is between 5-10% to 30%, you have to REORGANIZE your indexes. If the rate crosses 30%, i.e., it is within 30% to 100%, people usually REBUILD the indexes.

How to Reorganize Indexes

Using ALTER INDEX REORGANIZE command, users can reorganize their indexes and keep the index fragmentation in control. All indexes can be reorganized at a go by mentioning All during the command execution. When this command is running, the Tables can be used simultaneously. Irrespective of the Recovery Model, this operation gets completely logged in Transaction Log. Depending on the fragmentation rate, it can take more space in the Log.

How to Rebuild Indexes

When the database is highly fragmented, ALTER INDEX REBUILD command is used. It can be used to rebuild all the database indexes. During its execution, users cannot use the database. Based on the database recovery model, this gets included in the Log. In full recovery model, the operation takes a lot of space to get completed.

Conclusion

The article thoroughly discussed various approaches of managing SQL Server Database fragmentation. By reading the entire write-up, users will get a clear idea about the concept of SQL Server index fragmentation and they can secure their database from its adverse effect.