| Author |
Topic |
|
bab_ganesh
Starting Member
4 Posts |
 Posted - 2008-05-27 : 02:42:54
|
| Hi,Now i am doing a R&D on indexes in SQL 2005. I want to know clearly what is the actual uses of Fill Factor? What is advantage and disadvantage of it? Where it should use this fill factor?Please answer me as soon as possible.Ganesh. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-27 : 02:52:20
|
| Fill factor is the % of space in an index page that is used when the index is created or rebuilt. It's used to leave spme space open in index pages so that future inserts/updates don't split the page.The value that you use depends on the usage pattern of the table and the index keys--Gail Shaw |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-27 : 09:49:38
|
| Usually for high insert/updated table, Fill factor should be 60-70% whereas Low insert/update ,it should be high as recommended GilaMonster |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-27 : 13:12:19
|
| Depends on the index key. If I've got a cluster on an identity (always insert into the last page) even with frequent inserts I could get away with a very high fill factor. Of course, depending on what kind of updates I'm going to get. Index on a guid, different story.--Gail Shaw |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-27 : 13:19:53
|
| So GilaMonster? Is there any sources where i can learn regarding fillfactor in different scenerio and what would be optimal fillfactor for those situations? Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-27 : 13:21:45
|
quote:
Originally posted by sodeep
Usually for high insert/updated table, Fill factor should be 60-70% whereas Low insert/update ,it should be high as recommended GilaMonster
We did very thorough testing of different fill factors on a table that gets modified very frequently. This table has about 100 million rows in it and gets fragmented very quickly. When we dropped the fill factor down to even 80 or 90, we saw very slow performance on reads. Not only did it hinder our read performance, but it also hindered our write performance just not as drastically for writes. SQL Server Books Online says to keep the fill factor at 100% for almost all situations. It also says it is very rare to change it. Based on our testing, we agree. If you think you want to lower your fill factor settings, I would recommend performing very thorough testing on it as you may see drastic performance decreases like we did. We've kept all of our fill factors at 100% due to our testing and just defragment our indexes nightly rather than weekly.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-27 : 15:06:51
|
| Thanks. That's good to know.--Gail Shaw |
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-05-28 : 09:56:33
|
| The SQL Server 2005 Database Engine does not dynamically keep the specified percentage of empty space in the pages. Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered.Fill factor values 0 and 100 are the same in all respects.The fill factor setting applies only when the index is created, or rebuilt.ManojMCP, MCTS |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-28 : 10:03:18
|
| Mdubey,I don't know what is the problem with you. It doesn't make sense to repeat same answer like tape recorder. I think so many people has already responded you about this. If you give new ideas and answer, you are more than welcome but repeating same answer again and again make people irritating.I don't want you to get locked down. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-05-28 : 10:41:27
|
| Looks like new information to me.... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-28 : 11:13:43
|
| Gilamonster has already explained about it. |
|
|
|
TallCowboy0614
Starting Member
17 Posts |
Posted - 2008-05-30 : 16:58:17
|
| Looks like new information to me.Maybe someone *wink, wink, nudge, nudge* grabbed the sandpaper instead of the toilet paper this morning?_________________________________aka "Paul"Non est ei similis. "He's not the Messiah. He's a very naughty boy!" - Brian's mum |
|
|
|
Tins30
Starting Member
2 Posts |
Posted - 2009-01-15 : 10:33:42
|
| You specify the fill factor when you create the clustered index, and you can change it later if you want. If you specify 100, the data page is filled to nearly 100 percent, with room for only one record at the bottom of the page (it seems strange, but that’s how SQL Server views 100 percent full). What happens when a data page fills completely? When you need to insert data into a page that has become completely full, SQL Server performs a page split. This means SQL Server takes approximately half the data from the full page and moves it to an empty page, thus creating two half-full pages (or to half-empty pages, depending on how you look at it). Now you have plenty of room for the new data, but you have to contend with a new problem. Remember that this clustered index is a doubly linked list, with each page having a link to the page before it and a link to the page after it. So, when SQL Server splits a page, it must also update the headers at the top of each page to reflect the new location of the data that has been moved. Because this new page can be anywhere in the database file, the links on the pages don’t necessarily point to the next physical page on the disk. A link may point to a different extent altogether, which can slow the system. So you can see how this might slow the system down and so you need to configure the fill factor to avoid excessive page splits.Conclusion:Someone correct me if I am wrong. To my understanding, page-split can occur only when there is clustered index defined. Since Sql server needs to physically rearrange data after every Insert, Update and Delete. In short, if you have no index or just have non-clustered index defined then fill-factor won't play much of an importance here. |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2009-01-15 : 10:47:44
|
| I don't understand this partHow come FillFactor = 0 is same as Fillfactor = 100 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-15 : 10:56:04
|
There is minor difference.Read this: http://doc.ddart.net/mssql/sql70/1_server_35.htm |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2009-01-15 : 11:16:29
|
| OK I think, 100 Fill Factor = Page is 100% Full (i.e. 0 Percent Empty) |
|
|
|
Tins30
Starting Member
2 Posts |
Posted - 2009-01-16 : 11:26:14
|
| If you specify 100, the data page is filled to nearly 100 percent, with room for only one record at the bottom of the page (it seems strange, but that’s how SQL Server views 100 percent full).Now let's say 0% is not same as 100%. Do you think you really want to have data page with no data? Don't think so.. Hence fill specification(fill factor) 0% is considered same as 100%. |
|
|
|
|
Pl explain about Fill Factor in sql 2005