Identifying and Resolving SQL Server Index Fragmentation

Identifying index fragmentation is a crucial task for any database administrator. In this article, we explore how to spot and resolve it SQL Server.

Table Of Contents

• The reason for variations in Index Fragmentation percentage
• Improving DB performance and resolving Index Fragmentation
• How to determine the usage of the equation?
• Conclusions

In this SQL technology guide, we are exploring how to spot and resolve the Index Fragmentation in SQL Server. As the administrators know, identification of Index fragmentation and maintenance of the same is crucial tasks in are important parts of the database maintenance task.

MS SQL Server always updates index statistics with the Insertion, Updating, or Deletion activities over the tables. Index fragmentation is the index performance value described in percentage, which can be easily fetched by the SQL Server DMV. Based on the index performance value, the users can easily take the indexes in maintenance by merely revising the fragmentation percentage using the Reorganize or Rebuild operations.

The reason for variations in Index Fragmentation percentage

There could be variations in Index Fragmentation percentage while the page orders may not coordinate with actual physical page orders in the index page allocation. On data modifications in the table, information may bet resized on the data page. The page may be top full even before the update operation is executed don the table.

However, there could also be free space on the data page with an update operation performed on the table. The users can then observe the page orders on the table by running a massive deletion operation. The page may not be a top-full page or empty page along with these updating and deletion operations. So, the free space which is non-utilized may tend to raise the order mismatch again between the logical page and the actual physical page, which increases the fragmentation and end up in compromised query performance. It may also tend to consume more server resources and also increase the load.

It is also essential to say that index fragmentation will adversely affect the query performance only with a page scan. In these cases, it will raise the chances of poor performance on other SQL requests, and queries with high-fragmented indices over the table may take additional time to execute and may consume a lot more resources like CPU, Cache, and IO. So, the rest of the given SQL requests may find it tough to complete the operation by using the inconsistent server resources. Even blockage of tasks may occur by running the Delete or Update operations. The optimizer may not gather any information on index fragmentation on generating the given query execution plan.

Improving DB performance and resolving Index Fragmentation

Given this case, there may be many indexes getting created on a given table with combinations of different columns, and each of these indexes may show varying fragmentation percentages. So, before making it appropriate or considering an index that is in maintenance, the users may have to find the right threshold value given database. For performance tuning of your enterprise database there are advanced solutions you can choose, such as RemoteDBA.com. Providers like remote DBA have gained appropriate skills and expertise in performance tuning, database fragmentation, troubleshooting, and various other tasks by handling many projects with a subtle combination of all these use cases.

To find the object details, you can ideally use the below-given T-SQL statement:

We can see that it is noticeable to be 99% as the maximum average fragmentation, which may be engaged with another action to reduce fragmentation with different choices as REBUILD or REORGANIZE. These are commands for index maintenance, which can be executed inside the statement of ALTER INDEX. The users can also execute this command using the SSMS.

You may also note that REORGANIZE and REBUILD are two choices to execute the trim operation over the given page. However, this operation needs to be executed only at the off-peak hours to avoid any adverse impact on the users or transactions. The Enterprise Edition of Microsoft SQL Server supports the indexes online, and the offline features are supported with the REBUILD index.

INDEX REBUILD tends to drop the index and also reproduces the same with new index pages. However, this task can be run simultaneously using the online option, which is available in the Enterprise Edition by using the ALTER INDEX command. However, this may not affect the run request and the tasks running on other similar tables. REBUILD Index can be set offline also by using the below command:

• Offline:
  ALTER INDEX Index_Name ON Table_Name REBUILD
• Online:
  ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)

On performing REBUILD INDEX offline, the same index's object resource table may not be accessible until the completion of the REBUILD process. However, this may affect many other transactions, too, which are running and associated with this object. Rebuild index operations can also recreate the index. So, it can generate a whole-new statistic and then append the same log records of the index in the log files for a database transaction.

As we can see, there is a significant difference between Index REORGANIZE and REBUILD options. The database users have a choice among any one of these alternatives, a per index fragmentation percentage. There is no documented standard for this, but the admin needs to follow the standard equation based on the index size and type requirements.

How to determine the usage of the equation?

When it comes to index fragmentation percentage, it is always a question of how to determine the equation’s usage. Below mentioned as the common determination of the use of the equation:

• If you get the Fragmentation percentage between 15 to 30: REORGANIZE
• If you find a Fragmentation percentage of more than 30: REBUILD

In any given case, the REBUILD option can be more useful and thriving in the ONLINE option when the database is not made available to index maintenance during off-peak times.

Conclusions

To conclude, we can see that Index Fragmentation remains a critical internal fragmentation of the data files. The core parameters one need to consider while assessing the performance of your databases are like:

• Database architecture plays a crucial role in enterprise database planning.
• Database design based on the nature of data it holds and the storage plans
• Proper query writing

An ideal index design with proper maintenance from time to time will always boost query performance in the given database engine. So make a wise choice for your business.

You can read the orginal version of this article at Codemotion.com, where you will find more related contents. https://www.codemotion.com/magazine/dev-hub/backend-dev/sql-server-index-fragmentation/