Indexes rebuilding problem

  • Hello SQL community,

    I've a problem with my SQL Server 2005 database.

    On certain tables, we have declared indexes which are present in both tables : sys.sysindexes and sys.sysobjects. (Present in sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) table too).

    However, some indexes are present in :

    - sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)

    - sys.sysindexes

    But not in

    - sys.sysobjects

    Consequently, we have implemented a batch script which make the reorg or the rebuild of the indexes.

    For the indexes who are not listed in sys.sysobjects table, the script don't see the index to rebuild. So, some indexes (very important indexes :w00t:) are never rebuiled !!

    I would like to understand why some indexes are not listed in sys.sysobjects table ?

    Here is the code of the rebuild indexes script :

    DECLARE @base VARCHAR(100),@diff VARCHAR(100), @date_actuelle datetime,@command VARCHAR(8000),@pre_name_table VARCHAR(150),@name_index VARCHAR(150),@name_table VARCHAR(150),@id_index int,@avg_fragmentation_in_percent int

    DECLARE @i int

    SET @i = 0

    SET @date_actuelle = GETDATE()

    DECLARE name_index CURSOR FOR

    SELECT a.index_id, b.name, avg_fragmentation_in_percent, c.name, d.name,e.name

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,

    NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

    JOIN sys.objects AS c ON c.object_id IN (SELECT DISTINCT parent_object_id FROM sys.objects WHERE c.object_id=a.object_id )

    JOIN sys.schemas AS d ON d.schema_id = c.schema_id

    JOIN sys.databases AS e ON e.database_id = DB_ID()

    WHERE a.avg_fragmentation_in_percent BETWEEN 5 AND 100 AND a.index_id > 0 AND a.page_count > 8 ORDER BY a.avg_fragmentation_in_percent;

    OPEN name_index

    FETCH name_index INTO @id_index, @name_index, @avg_fragmentation_in_percent, @name_table,@pre_name_table, @base

    SELECT @command = N'ALTER DATABASE '+ @base + N' SET RECOVERY BULK_LOGGED';

    PRINT @command

    EXEC(@command);

    PRINT ''

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'Nom de l index : '+@name_index

    PRINT ''

    IF @avg_fragmentation_in_percent <= 30

    BEGIN

    PRINT 'TYPE DE REINDEXATION : ALTER REORG'

    PRINT convert(varchar,GETDATE(),131)

    SELECT @command = 'ALTER INDEX ' + @name_index + ' ON ' + @pre_name_table + '.' + @name_table + ' REORGANIZE WITH (LOB_COMPACTION=ON)' ;

    PRINT @command

    PRINT '-------------------------------------------------------------------------'

    PRINT ''

    SET @i=@i+1

    EXEC (@command);

    END

    IF @avg_fragmentation_in_percent >= 30

    BEGIN

    PRINT 'TYPE DE REINDEXATION : ALTER REBUILD'

    PRINT convert(varchar,GETDATE(),131)

    SELECT @command = 'ALTER INDEX ' + @name_index + ' ON ' + @pre_name_table + '.' + @name_table + ' REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF, MAXDOP=0, SORT_IN_TEMPDB=ON )';

    PRINT @command

    PRINT '-------------------------------------------------------------------------'

    PRINT ''

    SET @i=@i+1

    EXEC (@command);

    END

    FETCH name_index INTO @id_index,@name_index,@avg_fragmentation_in_percent,@name_table,@pre_name_table, @base

    END

    PRINT 'FIN DE LA REINDEXATION'

    PRINT convert(varchar,GETDATE(),131)

    SELECT @command = N'ALTER DATABASE '+ @base + N' SET RECOVERY FULL';

    PRINT @command

    EXEC(@command);

    CLOSE name_index

    DEALLOCATE name_index

    PRINT ''

    PRINT N'Nombre d index réindexé :'+cast(@i AS varchar(50))

    SELECT @diff = DATEDIFF(ss,@date_actuelle,GETDATE())

    PRINT N'Temps de maintenance :'+ @diff +N' s'

    Thank you in advance.

    Littlesquall.

  • What are the index id's from sys.indexes? If they are 0, than it is a heap, which would not be in sys.objects. If they are > 255, they are lob, which also would not be in sys.objects.

  • In sysindexes table the Id of my index is :

    select indid from sys.sysindexes where name='My_index_name'

    >>> 11

    What is a "lob" and a "heap" ?

    littlesquall

  • Books Online is your friend 🙂

    As for your query ... this should fix ya up, let me know ...

    SELECT

    a.index_id

    ,b.name

    ,avg_fragmentation_in_percent

    ,c.name

    ,d.name

    ,e.name

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b

    ON a.object_id = b.object_id AND a.index_id = b.index_id

    AND a.index_id = b.index_id

    JOIN sys.objects AS c

    ON c.object_id = a.object_id

    JOIN sys.schemas AS d

    ON d.schema_id = c.schema_id

    JOIN sys.databases AS e

    ON e.database_id = DB_ID()

  • Thanks for your interest in my problem.

    Your proposition is near the final solution.

    But when I execute this query, the name of the indexes who are not in sys.sysobjects is returned with NULL value. (b.name field = NULL)

    So the

    the query to rebuild the indexes can't work with @name_index=NULL

    ALTER INDEX ' + @name_index + ' ON ' + @pre_name_table + '.' + @name_table + ' REORGANIZE WITH (LOB_COMPACTION=ON)'

    ...:crying:

  • Toss a where clause at the end for:

    WHERE a.index_id > 0

    AND a.index_id < 255

    That should have been the only thing causing you nulls.

  • Yes I don't need the table name (c.name)

    But I need the indexe name (b.name)

    However, with your request, the field b.name is returned with NULL value for the indexes which are not listed in sys.objects.

    And if I haven't the indexe name, I can't rebuilt it...

  • Well, you're not really using much of your query ... that and why are you changing your recovery models for this operation? The ALTER index does not bloat the log files like DBCC DBREINDEX/DEFRAG used to.

    You're also not taking into account partitions, the ability to reorganize or rebuild, as well as online operations. I'd recommend using the example in BOL and customizing it to your environment's needs.

    Anyways, here is a condensed version of yours ... :

    This should give you all your indexes ...

    SELECT

    a.index_id

    ,b.name

    ,a.avg_fragmentation_in_percent

    ,db_name(db_id()) AS [DBName]

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b

    ON a.object_id = b.object_id

    AND a.index_id = b.index_id

    WHERE a.index_id > 0

    AND a.index_id < 255

  • Thanks Adams for your help.

    I've solved my problem.

    I don't know why, but some indexes of my SQL2005 DB listed in sys.objects have the field parent_object_id = 0 in sys_objects.

    That why the script in my first post didn't manage some very important indexes.

    I've corrected my script with Microsoft recommandations in

    (http://msdn2.microsoft.com/en-us/library/ms188917.aspx)

    and now, all my indexes are managed by my script.

    So, find enclosed the corrected script :

    DECLARE @base VARCHAR(100),@diff VARCHAR(100), @date_actuelle datetime,@command VARCHAR(8000),@pre_name_table VARCHAR(150),@name_index VARCHAR(150),@name_table VARCHAR(150),@id_index int,@avg_fragmentation_in_percent int

    DECLARE @i int

    SET @i = 0

    SET @date_actuelle = GETDATE()

    DECLARE name_index CURSOR FOR

    SELECT

    a.index_id,

    b.name,

    avg_fragmentation_in_percent,

    c.name,

    d.name,

    e.name

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b

    ON a.object_id = b.object_id

    AND a.index_id = b.index_id

    JOIN sys.objects AS c

    ON c.object_id =a.object_id

    JOIN sys.schemas AS d

    ON d.schema_id = c.schema_id

    JOIN sys.databases AS e

    ON e.database_id = DB_ID()

    WHERE a.avg_fragmentation_in_percent BETWEEN 5 AND 100 AND a.index_id > 0 AND a.page_count > 8 ORDER BY a.avg_fragmentation_in_percent;

    OPEN name_index

    FETCH name_index INTO @id_index, @name_index, @avg_fragmentation_in_percent, @name_table,@pre_name_table, @base

    SELECT @command = N'ALTER DATABASE '+ @base + N' SET RECOVERY BULK_LOGGED';

    PRINT @command

    EXEC(@command);

    PRINT ''

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'Nom de l index : '+@name_index

    PRINT ''

    IF @avg_fragmentation_in_percent <= 30

    BEGIN

    PRINT 'TYPE DE REINDEXATION : ALTER REORG'

    PRINT convert(varchar,GETDATE(),103) + ' ' + convert(varchar,GETDATE(),14)

    SELECT @command = 'ALTER INDEX [' + @name_index + '] ON [' + @pre_name_table + '].[' + @name_table + '] REORGANIZE WITH (LOB_COMPACTION=ON)' ;

    PRINT @command

    PRINT '-------------------------------------------------------------------------'

    PRINT ''

    SET @i=@i+1

    EXEC (@command);

    END

    IF @avg_fragmentation_in_percent >= 30

    BEGIN

    PRINT 'TYPE DE REINDEXATION : ALTER REBUILD'

    PRINT convert(varchar,GETDATE(),103) + ' ' + convert(varchar,GETDATE(),14)

    SELECT @command = 'ALTER INDEX [' + @name_index + '] ON [' + @pre_name_table + '].[' + @name_table + '] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF, MAXDOP=0, SORT_IN_TEMPDB=ON )';

    PRINT @command

    PRINT '-------------------------------------------------------------------------'

    PRINT ''

    SET @i=@i+1

    EXEC (@command);

    END

    FETCH name_index INTO @id_index,@name_index,@avg_fragmentation_in_percent,@name_table,@pre_name_table, @base

    END

    PRINT 'FIN DE LA REINDEXATION'

    PRINT convert(varchar,GETDATE(),103) + ' ' + convert(varchar,GETDATE(),14)

    SELECT @command = N'ALTER DATABASE '+ @base + N' SET RECOVERY FULL';

    PRINT @command

    EXEC(@command);

    CLOSE name_index

    DEALLOCATE name_index

    PRINT ''

    PRINT N'Nombre d index réindexé : '+cast(@i AS varchar(50))

    SELECT @diff = DATEDIFF(ss,@date_actuelle,GETDATE())

    PRINT N'Temps de maintenance : '+ @diff +N' s'

    Littlesquall

    😉

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply