How to rebuild and reorganize indexes ?

Created by QNE Software Sdn bhd, Modified on Wed, 30 Jun, 2021 at 6:30 PM by QNE Software Sdn bhd

Scenario 

Searching and reading data is getting slower even after performed increased Disk Free Space and Disk Defragmentation but SSD is not an option then you can continue reading on how to detect database fragmentation and how to perform database defragmentation

Why need to perform rebuild and reorganize indexes?

>> To allow you to get the requested information quicker

When should perform rebuild and reorganize indexes?

>> When fragmentation is 30% or higher

Solution 

  1. Connect your database with SSMS (SQL Server Management Studio)

https://support.qne.com.my/a/solutions/articles/81000385373 

  1. Right-click your server instance in Object Explorer, and then select New Query


  1. To find out which are those indexes fragmentation percentage is 30% and higher

Paste the following T-SQL code snippet into the query window and Replace [SampleDB_Testing] to your database name:

 -----Replace [SampleDB_Testing] to your database name-----

USE [SampleDB_Testing] 

SELECT OBJECT_NAME(ips.OBJECT_ID)

 ,i.NAME

 ,ips.index_id

 ,index_type_desc

 ,avg_fragmentation_in_percent

 ,avg_page_space_used_in_percent

 ,page_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)

 AND (ips.index_id = i.index_id)

WHERE avg_fragmentation_in_percent >= 30

ORDER BY avg_fragmentation_in_percent DESC

  1. Execute the query by selecting Execute or selecting F5 on your keyboard


  1. The results of the indexes fragmentation is 30% and higher are displayed under the area where the text was entered.

  1. To Rebuild and reorganize indexes fragmentation percentage is 30% and higher

Right-click your server instance in Object Explorer, and then select New Query

  1. Paste the following T-SQL code snippet into the query window and Replace [SampleDB_Testing] to your database name:

-----Replace [SampleDB_Testing] to your database name-----

USE [SampleDB_Testing] 

declare @tableName nvarchar(500) 

declare @indexName nvarchar(500) 

declare @indexType nvarchar(55) 

declare @percentFragment decimal(11,2) 

 

declare FragmentedTableList cursor for 

 SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 

   ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 

   indexstats.avg_fragmentation_in_percent 

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

   INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id 

        AND ind.index_id = indexstats.index_id 

  WHERE 

-- indexstats.avg_fragmentation_in_percent , e.g. >30, you can specify any number in percent 

   indexstats.avg_fragmentation_in_percent > 5 

  AND ind.Name is not null 

  ORDER BY indexstats.avg_fragmentation_in_percent DESC 

 

    OPEN FragmentedTableList 

    FETCH NEXT FROM FragmentedTableList  

    INTO @tableName, @indexName, @indexType, @percentFragment 

 

    WHILE @@FETCH_STATUS = 0 

    BEGIN 

      print 'Processing ' + @indexName + 'on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented' 

       

      if(@percentFragment<= 30) 

      BEGIN 

            EXEC( 'ALTER INDEX ' +  @indexName + ' ON ' + @tableName + ' REBUILD; ') 

       print 'Finished reorganizing ' + @indexName + 'on table ' + @tableName 

      END 

      ELSE 

      BEGIN 

         EXEC( 'ALTER INDEX ' +  @indexName + ' ON ' + @tableName + ' REORGANIZE;') 

        print 'Finished rebuilding ' + @indexName + 'on table ' + @tableName 

      END   

      FETCH NEXT FROM FragmentedTableList  

        INTO @tableName, @indexName, @indexType, @percentFragment 

    END 

    CLOSE FragmentedTableList 

    DEALLOCATE FragmentedTableList


  1. Execute the query by selecting Execute or selecting F5 on your keyboard.

  1. After the query is complete, all indexes with fragmentation more than 29.99% is defragmented

Know more about Optimize index maintenance to improve query performance and reduce resource consumption


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article