A question on rebuilding indexes via table or by index?

  • I just wondering if there is any difference in rebuilding all the indexes in a database by

    Option 1

    Cycling through all the indexes in sys.tables

    select OBJECT_SCHEMA_NAME([object_id])+'.'+name from sys.tables

    where OBJECT_SCHEMA_NAME([object_id]) = 'mine'

    Then cycling through with

    'ALTER INDEX ALL ON mytable REBUILD WITH (FILLFACTOR = 90,MAXDOP=0)’ Or

    Option 2

    select i.name from sys.indexes AS i

    left outer join sys.objects AS o on i.object_id = o.object_id

    JOIN sys.schemas as s ON s.schema_id = o.schema_id

    where s.name = 'mine'

    Then cycling through with

    ‘ALTER INDEX IX_mytable_myindex ON mine.mytable REBUILD WITH (FILLFACTOR=90, MAXDOP=0)’

    Many thanks

  • In effect, no. In side effects, yes.

    Rebuild all indexes on a table and it's a single transaction so you need enough free log space for all of the indexes on that table. If the tables are small you probably won't notice that. If they're large...

    Personally I prefer to rebuild just what needs rebuilding, not everything. On a small DB with large downtime windows it's easier just to rebuild everything, on larger DBs it becomes impractical

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Also, I wouldn't recommend rebuilding with a blanket fill factor of 90 (or anything else). 90 is often a good choice, but not always - for example, if the index is very rarely updated, it may be more appropriate to choose a fill factor of 100. It's possible to write your code so that it rebuilds the index with the existing fill factor - that's what I would recommend doing.

    John

Viewing 3 posts - 1 through 2 (of 2 total)

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