In this SQL technology guide, we explore how to spot and resolve Index Fragmentation in SQL Server. As administrators will know, identification of Index fragmentation and maintenance of the same are crucial elements in database maintenance.
MS SQL Server always updates index statistics whenever an Insertion, Updating, or Deletion activity is performed on the table. Index fragmentation is the index performance value, described in percentages, which can easily be fetched by the SQL Server DMV. Based on the index performance value, users can easily correct the indexes in maintenance by simply 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 percentages because the page orders may not coordinate with the actual order of the physical pages in the page allocation index. If data is modified in the table, information may be resized on the data page. The page may be too full even before the update operation is executed throughout the table.
However, it’s possible to create free space on the data page by performing an update operation on the table. The users can then observe page order on the table by running a massive deletion operation. The page must not be either too full or empty when running these updating and deletion operations. Un-utilized free space may again cause an order mismatch between the logical page and the actual physical page, which increases fragmentation and ends up compromising query performance. It may also consume more server resources and tends to increase the load.
It is also essential to say that index fragmentation will adversely affect query performance only with a page scan. In this case, it will increase the chances of poor performance on other SQL requests, and queries with highly-fragmented indices across the table may take additional time to execute. Such queries may consume a lot more resources – CPU, Cache, and IO. The remaining SQL requests may find it tough to complete the operation using the inconsistent server resources. Tasks may even be blocked by running the Delete or Update operations. The optimizer may not gather any information on index fragmentation while generating the given query execution plan.
Improving DB performance and resolving Index Fragmentation
In this case, there may be many indexes created on a given table with combinations of different columns, and each of these indexes may show varying fragmentation percentages. Before deciding what is appropriate or considering an index that is in maintenance, users may have to find the right threshold value for the given database. For performance tuning of your enterprise database there are advanced solutions, such as RemoteDBA.com. Providers like Remote DBA have gained the relevant 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.
Finding the object details ideally uses the T-SQL statement given below:
It is noticeable that 99% is the maximum average fragmentation, which may be engaged with another action to reduce fragmentation using choices such as REBUILD or REORGANIZE. These are commands for index maintenance, which can be executed within the statement of ALTER INDEX. 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 during off-peak hours only to avoid any adverse impact on users or transactions. The Enterprise Edition of Microsoft SQL Server supports the online indexes, and the offline features are supported by the REBUILD index.
INDEX REBUILD tends to first drop the index and then reproduce it with new index pages. However, this task can be run simultaneously using the online option, which is available in the Enterprise Edition using the ALTER INDEX command. However, this may not affect the run request or tasks running on other similar tables. The REBUILD Index can also be set offline by using the command below:
- 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 also affect other transactions that are running and associated with this object. Rebuild index operations can also recreate the index, generate a completely new statistic and then append the same log records from the index in the log files as a database transaction.
As we can see, there is a significant difference between Index REORGANIZE and REBUILD options. Database users have a choice of any one of these alternatives, according to the index fragmentation percentage. There is no documented standard, but the admin needs to follow the standard equation based on index size and type requirements.
How to determine the usage of the equation?
When it comes to index fragmentation percentages, it is always a question of how to determine the equation’s usage. The percentages commonly used to determine which equation to use are:
- If you get a 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. It thrives in the ONLINE option when the database is not made available for index maintenance during off-peak times.
Conclusions
It’s clear that Index Fragmentation remains a critical internal fragmentation of the data files. The core parameters one need to consider while assessing the performance of databases are:
- 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, properly maintained from time to time will always boost query performance in the given database engine, so make a wise choice for your business.
