How to check index fragmentation and resolve it

Index Fragmentation

In this article, you will learn how to identify and resolve Index Fragmentation in SQL Server. Index fragmentation identification and index maintenance are important parts of the database maintenance task. Microsoft SQL Server keeps updating the index statistics with the Insert, Update or Delete activity over the table. The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. According to the index performance value, users can take the indexes in maintenance by revising the fragmentation percentage with the help of Rebuild or Reorganize operation.

* Find Index Fragmentation status using the T-SQL statement

SELECT t.name as Table_name,
b.name as Index_name,ps.index_type_desc as Index_type,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id INNER JOIN sys.tables as t on b.OBJECT_ID = t.OBJECT_ID
WHERE   b.name not like '%null%' --(this condition for not in heap indexes)
--and t.name ='UserRight'---(For single table index fragmentation , enter a table name)
and ps.avg_fragmentation_in_percent > 0

*REBUILD INDEX

INDEX REBUILD always drops the index and reproduce it with new index pages. This activity can be run in parallel using an online option (Enterprise Edition) with the ALTER INDEX command, which does not affect the running requests and tasks of a similar table.

 REBUILD Index can be set online or offline using the below SQL commands:

 --Basic Rebuild Command

 ALTER INDEX Index_Name ON Table_Name REBUILD

 --REBUILD Index with ONLINE OPTION

 ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)

If a user performs the REBUILD INDEX offline, then the object resource (Table) of the index won’t be accessible till the end of REBUILD process completion. It affects numerous other transactions as well, which are associated with this object. Rebuild index operation recreates the index. Therefore, it generates new statistics and appends the log records of the index in the database transaction log file too.

REBUILD clustered index over the table affects other indexes of the table as well because the REBUILD clustered index rebuilds the non-clustered index of the table as well. Perform rebuild operation on all indexes of the table or database together; a user can use DBCC DBREINDEX() command.
 

DBCC DBREINDEX ('DatabaseName', 'TableName');

*REORGANIZED INDEX

The REORGANIZE INDEX command reorders the index page by expelling the free or unused space on the page. Ideally, index pages are reordered physically in the data file. REORGANIZE does not drop and create the index but simply restructure the information on the page. REORGANIZE does not have any offline choice, and REORGANIZE does not affect the statistics compared to the REBUILD option. REORGANIZE performs online always.

 ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE

To perform the REORGANIZE index operation on all indexes of the table or database together, the user can use the DBCC INDEXDEFRAG() command:

DBCC INDEXDEFRAG('DatabaseName', 'TableName');

REBUILD option is more useful with the ONLINE option when the database is not available to take index maintenance in off-peak hours.

*Conclusion

Index Fragmentation is an internal fragmentation in the data file. Core parameters of quick performance of your database are the Database Architecture, Database Design, and Query writing. A Good index design with maintenance always boosts the query performance in the database engine.