DEV Community

nisargupadhyay87
nisargupadhyay87

Posted on

Defragment Your Indexes for Better Database Performance

Index fragmentation occurs when the physical order of the index pages does not match the logical ordering of the index. When we insert, update or delete any data from the SQL Server table, the SQL Server automatically updates the table's indexes.

For example, when inserting records in a table, a page split might occur to accommodate the data in 8K pages of index, and eventually, the data becomes scattered. Suppose we have a table with 1500 pages that are 100% full. But due to various delete and update operations, now the table has 2000 pages, which are 60% full.

Due to that, when we access data, instead of reading data from 1500 pages, the SQL has to read 2000 pages. Also, the pages are scattered, so SQL Server has to perform several random IO operations. Note that the performance of the storage subsystem is always better when a sequential IO is performed in the data retrieval process. Due to index fragmentation, random I/O is performed, which adds overhead to storage.

The index fragmentation can be fixed by using the following methods

  • Index Reorganize
  • Index Rebuild

What is Index Rebuild

The index rebuild operation drops and recreates an index. The index rebuild blocks access to the table whose index we are rebuilding; therefore, the table will be inaccessible, and the performance will be degraded. We can rebuild the index without blocking access using the ONLINE = ON option, which is only supported in the SQL Server enterprise edition. Index rebuild creates new statistics, and the changes will be recorded in the Transaction Log file; therefore, you must keep track of the transaction log’s growth while rebuilding a large index.

To gain the optimum performance, the index should be rebuilt when the index fragmentation is higher than 30% to gain optimum performance.

What is index reorganize

The index reorganize is a lightweight index maintenance operation that reorders the physical pages according to the logical ordering of the indexes. The reorganize operation does not block access to the table. The index reorganize operation never drops the index, so the statistics will not be updated.

To gain the optimum performance, the index reorganize should be performed when the index fragmentation is between 10% and 30%.

We can use the following methods to defrag the indexes of the SQL Server.

  • ALTER INDEX T-SQL statement.
  • Using SQL Server management studio.

    Devart Index Manager

    In this article, we will learn how to reduce the fragmentation of indexes using the Devart SQL Index manager. The Devart index manager is a part of the Devart dbForge SQL tool professional. The Devart Index manager provides the following benefits

  • It is a central place where you can see a database list of all fragmented indexes. You can connect to the different database servers using the connection manager and populate the list of fragmented indexes from all databases.

  • You can rebuild all or specific indexes using a user-friendly interface and easy to use. Complex scripting is not required.

  • You can generate a CSV report of fragmented indexes and use it for review.

  • You can generate a SQL script to rebuild or reorganize fragmented indexes. You can use those scripts to automate index maintenance.

  • It is a SQL Server management studio plugin, so you do not have to maintain the additional software.

How to use SQL Index Manager

Open SQL Server Management Studio → Connect to SQL Server database engine → Expand Databases *→ Right-click on **WideWorldImportors *→ Hover **Index Manager → Select Manage Index Fragmentation.

Image description

The Devart Index Manager opens in a new SQL Server Management Studio tab. It is divided into three ribbons. The first ribbon contains the following configuration parameters.

  • Search Textbox: If the database has a lot of indexes, you can enter the keyword of the tables or index name. The tool will populate the index's name containing the specified keyword.

  • Reanalyze: This button scans the entire database and populates the list of all indexes.

  • Options: Here, you can configure the following index rebuild options.

  1. Reorganize threshold: Specify the index reorganize threshold. The index will be reorganized if the fragmentation is higher than the specified value.
  2. Rebuild threshold: Specify the index rebuild threshold. The index will be rebuilt if the fragmentation exceeds the specified value.
  3. Sort in tempdb: If you enable the Sort in tempdb option, the index rebuild operation will be performed in SQL Server temporary database (TempDB)
  4. Online: When enabling this option, the index rebuild operation will be performed without locking the table. This feature is only supported in the SQL Server enterprise edition.
  • Database Name: The list of databases created in a server.
  • Server Name: The list of SQL Server instance names.

The third section is a grid view which contains the following details:

  • Index Name: The list of fragmented Indexes above the specified threshold.
  • Fix Type: The fix type is an action suggested by the Devart Index Manager. The fix type is Index Reorganize or Index Rebuild. The fix type is also determined based on the threshold specified in the option.
  • Owner Object: The list of tables and schema whose indexes are fragmented. A table contains multiple indexes, but you can only view those with fragmentation higher than the specified threshold.
  • Index Type: Type of Index. It can be clustered index or a non-clustered index.
  • Index Size (Pages): Size of fragmented indexes. The size is 8kb pages.
  • Index Size (MB): Size of fragmented indexes. The size is in MB.
  • Fragmentation: Percentage fragmentation in listed indexes.
  • Partition number: Partition number of the table.
  • Rows in Partition: Total records in a partition.
  • Reason: Reason for index maintenance (Index rebuild to index reorganize).

The following screenshot shows the list of fragmented indexes in the WideWorldImportors database.

Image description

In this demo, we want to reorganize the indexes of the WideworldImportors database when fragmentation is higher than 10%, rebuild the indexes when fragmentation is higher than 25%, and the maximum index size must be higher than 1000 Pages.
To do that, we will change the index rebuild, reorganize the threshold, and specify the minimum index size.

To do that, Click on the Options button.

Image description

A dialog box opens. Enter 10% in Reorganize threshold, 25% in the Rebuild threshold, and 1000 in the Minimum index size textbox. Enable the Sort in TempDB option. Click OK to save the changes.

Image description

Now, we will rescan all tables and populate the list of the fragmented index. Click on Analyze.

Image description

The Devart index manager will populate the list of fragmented indexes which matches the above criteria. Following is the list in a grid view.

Image description

Before we rebuild the indexes, you can see three options in a ribbon that will be enabled.

  • Fix: When we click on Fix, the Index Manager will automatically rebuild/reorganize the index.
  • Script Changes: When we click on Script Changes, the index manager will generate an ALTER INDEX REBUILD script. This option gives us the flexibility to change the index rebuild parameters.

See the following screenshot:

Image description

We will script the changes in this demo, so I selected all indexes and clicked the Script Changes button. See the following image:

Image description

A SQL Script to rebuild the indexes has been created.

Image description

We can execute the script immediately, or we can create a SQL job to execute it during off-business hours.

Summary

This article explains the SQL Server indexes, how they get fragmented, and how to defrag them using the Devart dbForge Index manager.

Top comments (0)