May 9, 2006 at 8:58 am
Hello,
MSSQL 2000/SP4
I plan to create a job to run the following code:
EXEC sp_msforeachdb 'DBCC REINDEX (?) '
The maintainance goal is to have the indexes rebuilt for all tables, in all databases, with a fillfactor of 80% to 90%.
Without specifying a value, what would be the FILLFACTOR if I run the above code? Any other suggestions greatly appreciated.
Many thanks. Jeff
May 9, 2006 at 9:12 am
From BOL: When fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified when the index was created.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_94mw.asp
May 9, 2006 at 9:17 am
Hi,
So if the vaule in DBREINDEX is omitted, it's the same as entering a 0? Also, if no FILLFACTOR was specified during INDEX creation, what would be the default value? Is there a way to determine what the FILLFACTOR was at INDEX creation?
Many thanks. Jeff
May 9, 2006 at 9:34 am
If I recall corrently, the default fill factor is 80% (going purely on memory here).
May 9, 2006 at 9:36 am
From BOL:
The default is 0. If fillfactor is 100 or 0, the Database Engine creates indexes with leaf pages filled to capacity, but some space remains within the upper level of the index tree to allow for at least one additional index row.
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
Look at this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8sit.asp
for information on the sysindexes table
May 9, 2006 at 9:45 am
Sorry, was remembering what I usually use for clustered indexes.
Here's a good explanation:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_9ak5.asp
May 9, 2006 at 10:02 am
Not just you, Chris. I had it somewhere in my head that it was something like that, too. I just couldn't remember the value (thought it was 70 or 80) so I did some research to find the value and discovered what I quoted.
May 9, 2006 at 10:04 am
Definitely an arguement for setting the server-wide fill factor setting. Unless you are running a read-only database, the default doesn't make a lot of sense.
May 10, 2006 at 7:50 am
Hi guys,
many thanks for the knowledge transfer. I agree that it might be best to go for a server-wide FILLFACTOR setting ... any suggestions ie: 70%? 80%??
Many thanks. Jeff
May 10, 2006 at 7:52 am
I'd go with 80, and maybe specify fill factors for specific indexes (heavily hit tables get a lower number, Read Only ones get 100, etc...) individually. YMMV...
May 11, 2006 at 8:02 am
Hi,
Is there a method to re-index a particular FileGroup? ie: just the PRIMARY and not others ie: FG2; FG3 (these are set to READ ONLY).
Many thanks. Jeff
May 11, 2006 at 8:06 am
I think it is on a table or index basis:
DBCC DBREINDEX
( 'database.owner.table_name'
[ , index_name
[ , fillfactor ]
]
) [ WITH NO_INFOMSGS ]
You might find a script in the script section that would run a cursor and reindex all tables in a particular filegroup.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply