Rebuilding Fragmented Indexes


At PBTI, we have never been overly happy with the speed that Syteline Runs at. We do have a lot of data, and we do process a lot of transactions. That said, we took infor’s proposed hardware suggestions and multiplied everything – more ram, bigger faster processor, the fastest disks, and the best raid configuration ….. still it seems sluggish! We even have a separate application server, but this had little or no impact. The only thing I can see that will make a big improvement is to upgrade the Windows O/S as 2008 R2 standard edition only recognises 32Gb Ram, getting more of the databases(s) into RAM would probably enhance the situation.

This week I’m looking at any optimisation tweaks I can make – Creating new indexes is always a good idea, but you also need to maintain the ones you have. A simple maintenance plan doesn’t work so be aware! There is a Knowledgebase on InforXtreme you need to read 657685. This has 3 attached T-SQL scripts that you need to create a custom maintenance plan with – it uses DBCC INDEXDEFRAG to defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, therefore improving index-scanning performance. HOWEVER using the following query will show you it is not doing a great job :-

SELECT
DB_NAME(database_id)  + ‘.dbo.’ + object_name(object_id,database_id)

FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
where avg_fragmentation_in_percent > 33
/*and DB_NAME(database_id) = ‘Ireland_app’*/
order by   avg_fragmentation_in_percent desc

In my mind, you need to rebuild your indexes at regular intervals if they are above a pre-defined fragmentation level.

DO NOT USE THIS UNLESS YOU ARE CONFIDENT YOU KNOW WHAT YOU ARE DOING.  I cannot take any responsibility for any detrimental impact it has on your system !

The following script I wrote to parse through each table that has a fragmented index, and rebuilt the index accordingly. I started off with 4000+ tables, and ended with 2900

declare @fqn nvarchar(200) /*fully qualified name*/
declare @dothis as nvarchar(max) /*execution string */
declare tablereindex cursor for
SELECT
DB_NAME(database_id)  + ‘.dbo.’ + object_name(object_id,database_id)

FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
where avg_fragmentation_in_percent > 33
/*and DB_NAME(database_id) = ‘Ireland_app’*/
order by   avg_fragmentation_in_percent desc

OPEN tablereindex

FETCH NEXT
FROM tablereindex
INTO @fqn

WHILE @@FETCH_STATUS = 0
BEGIN

select @dothis =  ‘ALTER INDEX ALL ON ‘ + @fqn+ ‘ REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = Off)’
exec (@dothis)
FETCH NEXT
FROM tablereindex
INTO @fqn
END

CLOSE tablereindex
DEALLOCATE tablereindex

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.