Rebuild index to reduce Fragmentation in SQL Server

Thiyagu

6 months ago

Here we will learn how to identify and resolve by Rebuild index to reduce 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.

In SQL Server, both “rebuild” and “reorganize” refer to operations that can be performed on indexes to address fragmentation. However, they are distinct operations with different characteristics. Let’s explore the differences between rebuilding and reorganizing indexes:

Note: Optimize index is one of the maintenance activity to improve query performance and reduce resource consumption. Ensure you will plan to Performing the database index during the off business hours or less traffic hours (less request to database).

Advantages of Rebuild index to reduce Fragmentation:

Removes both internal and external fragmentation.
Reclaims unused space on data pages.
Updates statistics associated with the index.

Considerations:

Requires more system resources.
Locks the entire index during the rebuild process, potentially causing blocking.

How to find the Fragmentation?

Here we executed the SQL script for checking the fragmentation details for the specific database , where the result shown in the percentage.


## Script will give details of fragmentation in percentage 
Method 1:

DECLARE @cutoff_date DATETIME = DATEADD(day, -20, GETDATE()); SELECT OBJECT_NAME(ip.object_id) AS TableName,
i.name AS IndexName,
ip.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ip
JOIN sys.indexes i
ON ip.object_id = i.object_id AND ip.index_id = i.index_id
JOIN sys.dm_db_index_usage_stats ius
ON ip.object_id = ius.object_id AND ip.index_id = ius.index_id

Method 2:

SELECT
DB_NAME() AS DBName
,OBJECT_NAME(ps.object_id) AS TableName
,i.name AS IndexName
,ips.index_type_desc
,ips.avg_fragmentation_in_percent
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
ORDER BY ips.avg_fragmentation_in_percent DESC

## Script will give details of fragmentation in percentage

Method 1:

DECLARE @cutoff_date DATETIME = DATEADD(day, -20, GETDATE()); SELECT OBJECT_NAME(ip.object_id) AS TableName,

i.name AS IndexName,

ip.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ip

JOIN sys.indexes i

ON ip.object_id = i.object_id AND ip.index_id = i.index_id

JOIN sys.dm_db_index_usage_stats ius

ON ip.object_id = ius.object_id AND ip.index_id = ius.index_id

Method 2:

SELECT

DB_NAME() AS DBName

,OBJECT_NAME(ps.object_id) AS TableName

,i.name AS IndexName

,ips.index_type_desc

,ips.avg_fragmentation_in_percent

FROM sys.dm_db_partition_stats ps

INNER JOIN sys.indexes i

ON ps.object_id = i.object_id

AND ps.index_id = i.index_id

CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips

ORDER BY ips.avg_fragmentation_in_percent DESC

Rebuild index to reduce Fragmentation:

The REBUILD operation involves recreating the entire index. This process drops the existing index and builds a new one from scratch. During the rebuild, the index is effectively offline, and there can be a period of downtime where the index is not available for queries. In simple, REBUILD locks the table for the whole operation period (which may be hours and days if the table is large). The syntax for rebuilding an index is as follows:

Rebuilding Full Index on selected database:

After executing the Rebuild index on specific database, you can able to view the fragmentation is reduce as shown in the below image.


-- Rebuild ALL Indexes
-- This will rebuild all the indexes on all the tables in your database.

SET NOCOUNT ON
GO

DECLARE rebuildindexes CURSOR FOR
SELECT table_schema, table_name  
FROM information_schema.tables
	where TABLE_TYPE = 'BASE TABLE'
OPEN rebuildindexes

DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName

WHILE (@@FETCH_STATUS = 0)
BEGIN
   SET @Statement = 'ALTER INDEX ALL ON '  + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' REBUILD'
   --PRINT @Statement 
   EXEC sp_executesql @Statement  
   FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName
END

CLOSE rebuildindexes
DEALLOCATE rebuildindexes
GO
SET NOCOUNT OFF
GO

-- Rebuild ALL Indexes

-- This will rebuild all the indexes on all the tables in your database.

SET NOCOUNT ON

DECLARE rebuildindexes CURSOR FOR

SELECT table_schema, table_name

FROM information_schema.tables

where TABLE_TYPE = 'BASE TABLE'

OPEN rebuildindexes

DECLARE @tableSchema NVARCHAR(128)

DECLARE @tableName NVARCHAR(128)

DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName

WHILE (@@FETCH_STATUS = 0)

BEGIN

SET @Statement = 'ALTER INDEX ALL ON ' + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' REBUILD'

--PRINT @Statement

EXEC sp_executesql @Statement

FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName

END

CLOSE rebuildindexes

DEALLOCATE rebuildindexes

SET NOCOUNT OFF

Advantages of Rebuild index to reduce Fragmentation:

Considerations:

How to find the Fragmentation?

Rebuild index to reduce Fragmentation:

Rebuilding Full Index on selected database:

Other Popular Articles