Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
What is the best value for Index Fill Factor?
Message
 
 
To
21/10/2004 11:17:32
Victor Acosta
Independent Programmer.
San José, Costa Rica
General information
Forum:
Microsoft SQL Server
Category:
Database management
Miscellaneous
Thread ID:
00952900
Message ID:
00953588
Views:
17
Victor,

Thanks for pasting this from books on line but no matter how many times I read it, it doesn't recommend anything. I found something in the internet though which might also help you if you will need it.

http://www.quest-pipelines.com/newsletter-v5/0204_B.htm

>fill factor Option
>Use the fill factor option to specify how full Microsoft® SQL Server™ should make each page when it creates a new index using existing data. The fill factor percentage affects performance because SQL Server must take time to split pages when they fill up.
>The fill factor percentage is used only at the time the index is created. The pages are not maintained at any particular level of fullness.
>The default for fill factor is 0; valid values range from 0 through 100. A fill factor value of 0 does not mean that pages are 0 percent full. It is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. It is different from 100 in that SQL Server leaves some space within the upper level of the index tree. There is seldom a reason to change the default fill factor value because you can override it with the CREATE INDEX statement.
>Small fill factor values cause SQL Server to create new indexes with pages that are not full. For example, a fill factor value of 10 is a reasonable choice if you are creating an index on a table that you know contains only a small portion of the data that it will eventually hold. Smaller fill factor values cause each index to take more storage space, allowing room for subsequent insertions without requiring page splits.
>If you set fill factor to 100, SQL Server creates both clustered and nonclustered indexes with each page 100 percent full. Setting fill factor to 100 is suitable only for read-only tables, to which additional data is never added.
>fill factor is an advanced option. If you will be using the sp_configure system stored procedure to change the setting, you can change fill factor only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.
JESS S. BANAGA
Project Leader - SDD division
...shifting from VFP to C#.Net

CHARISMA simply means: "Be more concerned about making others feel good about themselves than you are in making them feel good about you."
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform