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!