SQL Server Rebuild and Reorganize Index

Hi

All Indexes in the SQL Server database will be fragmented After related tables got many update, delete transactions.

 

If the index in the table is fragmented then all transactions related with that table will be very slow. So SQL Server DBA should frequently monitor indexes and which of indexes are fragmented and which are not.

You can use fragmented index with following procedure. This procedure rebuild and reorganize index depends on its situation

 

 

CREATE PROC [INDEX_MAINTENANCE] @DBName VARCHAR(100)
AS BEGIN
            SET NOCOUNT ON;
            DECLARE
                  @OBJECT_ID INT,
                  @INDEX_NAME sysname,
                  @SCHEMA_NAME sysname,
                  @OBJECT_NAME sysname,
                  @AVG_FRAG float,
                  @command varchar(8000),
                  @RebuildCount int,
                  @ReOrganizeCount int
 
            CREATE TABLE #tempIM (
                  [ID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,
                  [INDEX_NAME] sysname NULL,
                  [OBJECT_ID] INT NULL,
                  [SCHEMA_NAME] sysname NULL,
                  [OBJECT_NAME] sysname NULL,
                  [AVG_FRAG] float
            )          
            SELECT @RebuildCount=0,@ReOrganizeCount=0
           
            --Get Fragentation values
            SELECT @command=
                  'Use ' + @DBName + ';
                  INSERT INTO #tempIM (OBJECT_ID, INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG)
                  SELECT
                        ps.object_id,
                        i.name as IndexName,
                        OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,
                        OBJECT_NAME (ps.object_id) as ObjectName,
                        ps.avg_fragmentation_in_percent
                  FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, ''LIMITED'') ps
                  INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
                  WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0
                        and ps.database_id=DB_ID('''+@DBName+''')
                  ORDER BY avg_fragmentation_in_percent desc
                  '
           
            exec(@command)
            DECLARE c CURSOR FAST_FORWARD FOR
                  SELECT OBJECT_ID,INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG
                  FROM #tempIM
            OPEN c
            FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG
            WHILE @@FETCH_STATUS = 0
            BEGIN
                  --Reorganize or Rebuild
                  IF @AVG_FRAG>30 BEGIN
                        SELECT @command = 'Use ' + @DBName + '; ALTER INDEX [' + @INDEX_NAME +'] ON ['
                                                 + @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] REBUILD   WITH (ONLINE = ON  )';
                        SET @RebuildCount = @RebuildCount+1
                  END ELSE BEGIN
                        SELECT @command = 'Use ' + @DBName + '; ALTER INDEX [' + @INDEX_NAME +'] ON ['
                                                 + @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] REORGANIZE ';
                        SET @ReOrganizeCount = @ReOrganizeCount+1
                  END
                                                
                  BEGIN TRY
                        EXEC (@command); 
                  END TRY
                  BEGIN CATCH
                  END CATCH
                 
                  FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG
            END
            CLOSE c
            DEALLOCATE c
           
            DROP TABLE #tempIM
           
            SELECT cast(@RebuildCount as varchar(5))+' index Rebuild,'+cast(@ReOrganizeCount as varchar(5))+' index Reorganize is done.' as Result
END  




  

You can execute this procedure with following query. Type Database name instead of DEVECI correctly, and procedure will rebuild and reorganize related databases indexes.

 

exec dbo.INDEX_MAINTENANCE 'DEVECI';



  

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

2 thoughts on “SQL Server Rebuild and Reorganize Index

  • May 18, 2019 at 1:39 pm
    Permalink

    I am usually to running a blog and i actually admire your content. The article has really peaks my interest. I am going to bookmark your website and keep checking for new information.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *