When to rebuild indexes in SQL-Server

Sometimes a table with the right indexes is still not performing like expected. At this moment you have to check the fragmentation of the indexes of the table.

If the fragmentation off the index is more than 30 it's recomended to rebuild the index, lower then 30 but still more than 10 it's recomended to re-organise the index.

From SQL2005 there is a system function which returns an overview of the indexes and their framentation count in percentages:

SELECT Object_Name(object_id) as tabelname, 
       dbo.index_name(object_id, index_id) as indexname, 
       object_id as indexnummer, 
       avg_fragmentation_in_percent as percentFragmentated
FROM sys.dm_db_index_physical_stats  (NULL, NULL, NULL, NULL , null);

Object_Name(object_id) is a system function to get the objectname from the object_id. There is not a function to retreive the index-name from the index_id, so created a custom function, like this:

CREATE FUNCTION [dbo].[index_name] (@object_id int, @index_id int) 
RETURNS sysname 
AS 
BEGIN 
  RETURN(SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id) 
END; 

With this information it's possible to store the indexes and their tablenames in a temp table and loop through it and rebuild the index if the fragmention is higher than 30 and reorganise the index if it's lower than 30. It's looking like this:

-- create the temp table
declare @percent int
declare @tabelname varchar(100)
declare @indexname varchar(100)
declare @command nvarchar(100)
declare @Rowcount int
declare @RowcountTotaal int
declare @FragmentedIndexes table 
(
    RowID int IDENTITY(1, 1), 
    tabelname varchar(100),
    indexname varchar(100),
    indexnummer int,
    percentFragmentated int
)

-- fill the table
insert into @FragmentedIndexes(
                 tabelname, 
                 indexname, 
                 indexnummer, 
                 percentFragmentated)
SELECT Object_Name(object_id) as tabelname, 
       dbo.index_name(object_id, 
       index_id) as indexname, 
       object_id as indexnummer, 
       avg_fragmentation_in_percent as percentFragmentated  
FROM sys.dm_db_index_physical_stats
  (DB_ID(N'Intres-AX40-O'), NULL, NULL, NULL , null);

-- detect totaal
Set @RowcountTotaal = (select count(*) from @FragmentedIndexes)
SET @RowCount = 1

-- select records
WHILE @RowCount30
                begin
                -- index moet gerebuild worden
                set @command = 'ALTER INDEX ' + @indexname + ' on ' +  + @tabelname +' REBUILD'
                EXECUTE sp_executesql @command
                print  @command
                set @command =''
                end
                
            if @percent > 10 AND @percent

If you run the script every night you indexes should be Ok!

{{opmerking.Naam}}:

{{opmerking.OpmerkingText}}

            

Saving your comment....

Naam is verplicht!
Email is verplicht!
Opmerking is verplicht!