Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2004-03-02 : 12:42:24
|
| Hi,One of the junior DBAs here set up some reindexing jobs by using a maintenance plan. Ok, not the greatest way to do it, but I give him credit for at least attempting some maintenance.The maint plan was set up to do "leaving 10% free space" and for the most part that appeared to work pretty well, except on one table....it went instead with 90% free space, causing the table to balloon in size from 1GB to 15GB (it had 13 indexes on it).I guess "OUCH" doesn't adequately describe this, but what the heck happened? I am guessing that the maintenance plan uses the original fill factor, but then tries to shrink it after the fact or something. Anyone have any evidence to back this idea up? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-02 : 12:45:34
|
| 10% FILLFACTOR is ridiculously too low. 90% is much better. FILLFACTORs are usually set to 80. If you don't specify the FILLFACTOR option in the DBCC DBREINDEX command, it uses the original FILLFACTOR (look at DBCC DBREINDEX in BOL for the info).I would suggest changing your fillfactors to whatever makes sense for your environment, but it won't be anything lower than 50 or probably even 70.Tara |
 |
|
|
MuadDBA
628 Posts |
Posted - 2004-03-02 : 12:52:16
|
| Oh I agree completely....the thing is, by everything I can tell, the original fill factor was 0...so I don't know what happened. And the maintenance plan was telling it to use a fillfactor of 90, so I don't know what happened there, either...all the other tables were 90% full.I would never use a 10% fill factor on an index...why bother with the index at that point? If the pages fill up that fast, it just means you need to reindex more frequently.I am going to restore a copy of the database from before I re-reindexed it and see what the fillfactors were, just in case someone changed them. My copy from 2 weeks ago shows fillfactor of 0. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-02 : 12:56:01
|
| 0 means 100% though. You only want that if your system never has any data changes.Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-02 : 13:02:11
|
| I know, what can I say, the vendor does some stupid things. The jDBA was probably going to make an improvement, but unfortunately something backfired. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-03 : 08:35:06
|
| Well, I restored my database last night to the pre-upgrade version and looked at the original fill factors on the table. They were, as I suspected, all set to 10. Unfortunately, even though the maintenance plan said to "change the "free space per page percentage to 10%", it apparently ignored that for this index and used a 10% fillfactor instead.Bottom line....make the jDBA aware of the pitfalls of using maintenance plans for reindexing....if he isn't already. |
|
|
|
|
|
|
|
|
Maint plan reindex fillfactor ignored?