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 |
|
raguyazhin
Posting Yak Master
105 Posts |
 Posted - 2010-12-03 : 05:32:21
|
| what is the use if fillfactor in Alter Index Command?how to decide which number we will use for fillfactor in Alter Index? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-03 : 05:38:32
|
| Good question. There is a school of thought that ways to set it to 100% all the time ...But I suppose the correct answer is "it depends" and only by testing, case-by-case, will you find the answer.If you have an index on an IDENTITY (or something else) which is always increasing then definitely set the Fill Factor to 100% |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-12-03 : 16:12:29
|
| All the fill factor does is tell SQL Server how full to make each page during that rebuild process. So, if you use a fill factor of 90, each page would be ~90% full at the end of the rebuild.Everytime a row in the table (clustered index) - or a value in the columns (non-clustered index) changes, the data on that page of the index will be updated. If you have a varchar value that changes in length, this may cause a page split if the page does not have enough space available to store the new values.If your index keys are not monotonically increasing - then every time a new row is inserted it would have to be inserted on that page. If there is no room on the page, a page split occurs.So, what you set the fill factor to can reduce the number of page splits and how often that happens. Arbitrarily setting the fill factor to 100% because you have an IDENTITY column is not necessarily correct. Again, it depends on the type of activity. If you never update data in that table - a 100% fill factor is fine. But, if you update data and have varchar values that will change - it is likely that your table will become fragmented and need to be rebuilt.Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-03 : 16:26:01
|
| Reducing the fillfactor from 100% should help out inserts if you are experiencing page splits. However, it can drastically reduce your select performance. We did extensive analysis on different fillfactor in hopes that we've see faster inserts. The improvement on inserts was very minor on our system, but the performance degradation on selects was massive. Due to this, we abandoned switching the fillfactor. All of our systems use 100% fillfactor for the indexes. We have hundreds of servers and thousands of databases.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-03 : 19:08:34
|
"So, what you set the fill factor to can reduce the number of page splits and how often that happens"I take a different view on this  If you set the Fill Factor to 90% you leave some "free space" on every page in the index.If you add a row it will not be necessary to split the page. But if you add two rows with similar-keys (or maybe ten ...) then a page split will happen anyway - because the page will be full. IME new inserts tend to be clustered - so it is as likely that there will be 1 as 10 within a single index page.Also, you have 10% free on every index page, but probably only a small number of those pages will have a new row added before the index is rebuilt again. So a waste on Select (as Tara said)If you use 100% Fill then the FIRST row inserted in each page will cause a page split - but then you can fill up the reminder of the page (which is likely IME as per my earlier comment about additions likely to be clustered).Hence I think that in the absence of good, careful, testing setting to 100% fill is a reasonable strategy. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-04 : 12:55:11
|
| A while back, after reading one of Tara's posts I did some pretty heavy duty testing on fillfactors and page splits.The performance degradation during read operations was easily measurable -- remember every page loaded causes disk (or memory) access AND throws 8KB at the processor to deal with. Interstingly I didn't get any significant performance gain on the writes, which is the reason for fillfactor in the first place.The test was structured to intentionally cause page splits.My conclusion was that the minor (if any) gain during write operations from using a fillfactor other than 100 is far outweighed by the penalty during read operations. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-04 : 20:18:12
|
| No, thank you. Until I saw you post on the subject, I would think nothing of putting fillfactors of 70 or 80 on nonclustered indexes.Of course, I couldn't just take your word for it, so I loaded up a few million records and started playing lol. |
|
|
|
|
|
|
|
|
Fill Factor