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 |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2004-08-02 : 00:40:34
|
| Hi ,I noticed that Index fill factor setting on one of our SQL servers is set to 100 % instead of the default . Is this really advisable to set to default ? The database on this server has constant inserts and updates every day though scheduled jobs . Would like to have some idea on how this setting could have performance impact on the server , or any recommendations to set this setting that might suit the database with constant updates . The server is SQL 2000 Enterprise Edition with SP3.Thanks . |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-02 : 00:50:47
|
| I would set it back to it's default. I can't imagine how many page/splits and fragmentation you must be getting with this.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2004-08-02 : 01:00:19
|
| Hi ,Thanks for your reply . Can I set the option to 100 % on read-only tables and the rest of the tables to default ?This has to be done , I think, through SP's during index creation . But will the settings in the SP's override the default server level setting ? I am confused !!! How does it work ??? |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-08-02 : 09:32:59
|
| Yes, if you set the fill factor on creation of an index it overrides the default setting for a server.Yes, you can set the fillfactor to 100% for read-only tables.You should also check the default fill factor on your server to make sure it's a reasonable value. I had someone once who inadvertently set the default fill factor to 10% (!!!!!). The next time we rebuilt indexes, we wondered what the F happened. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-02 : 10:14:05
|
| lol Bet that was fun.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-08-02 : 14:07:15
|
| You can imagine how irate I was with the application vendor when our DB blew up (it just so happened that the next time we reindexed was after a bloody upgrade, so I blamed the upgrade). Then you can imagine how sheepish I was when I found out what the problem really was."Fun" is a word you could use, but I think it would be the wrong word. |
|
|
|
|
|
|
|
|
Index fill factor setting .