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 |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2007-02-28 : 09:59:44
|
| guys,When I create an Index I will have to specify fill factor. HOw does the fill factor effect the performance of the indexIf I have an index on unique column values what shoulf be the value of the fil factorand how does the value change when an index is created on 3 or more columns.In sql server 2005 when I created a primary key on an clustered/non clustered index the fill factor by default is 0. Should it changed??any suggestions/inputs would helpThanks |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-02-28 : 11:44:20
|
See Fill Factor in Books Online it answers all of your questions, but basically fill factor determines how full the data pages (pages are 8K in size) of the table in the database are when you create or rebuild an index. Generally - for better read performance you want full pages because then less pages need to be read in order to retrieve the rows. However, write performance is better when new rows can be added to existing pages instead of having to allocate new pages and move rows around to make space for the new row - a page split. Fill factor lets you leave space open in the pages so that new rows can be inserted with fewer new page allocations and pages splits (at the expense of slower read performance). Note that if the new rows are all going to the end of the index then page splits don't have to occur because new rows are not inserted between existing ones. quote:
When I create an Index I will have to specify fill factor. HOw does the fill factor effect the performance of the index
See my explanation above. quote:
If I have an index on unique column values what shoulf be the value of the fil factorand how does the value change when an index is created on 3 or more columns.
Those are not really determining factors in what fill factor you should use, more important is do you need optimal read or write or balanced performance, and are you inserting new values at the end of the index or randomly all over the table - if random then a lower fill factor (ie. more free space) will be better. quote:
In sql server 2005 when I created a primary key on an clustered/non clustered index the fill factor by default is 0. Should it changed??
You didn't create a primary key on a clustered/non-clustered index. You create a primary key and an index gets created in order to enforce the uniqueness that is required for the primary key - you can choose to make that index clustered or non-clustered. Again determining fill factor has nothing to do with whether it's an index for a primary key or not, it's about determining which performance (read or write) is more important, and the nature of your inserts. Random inserts need more free space, inserts at the end of the index need less because the new rows are always going to the same page anyway. |
 |
|
|
|
|
|
fill factor