September 3, 2012 at 4:26 am
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
September 3, 2012 at 4:40 am
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
September 3, 2012 at 7:53 am
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