SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 fill factor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 02/28/2007 :  09:59:44  Show Profile  Reply with Quote
guys,

When I create an Index I will have to specify fill factor. HOw does the fill factor effect the performance of the index
If I have an index on unique column values what shoulf be the value of the fil factor
and 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 help

Thanks

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 02/28/2007 :  11:44:20  Show Profile  Reply with Quote
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 factor
and 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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000