December 31, 2011 at 1:38 am
I have a indexes and fill factor is 80 if ı will make 95 ı have space in data file sql server engine use this space
December 31, 2011 at 1:52 am
How big is the table? What’s the use of table (transaction / master)?
December 31, 2011 at 1:59 am
I have a lot of indexes i want to learn if I need shirink fill factor 80 to 95 to use data space...
December 31, 2011 at 2:32 am
I can’t help much if you don’t provide the details. If you want to learn it, I can suggest you few articles (on BOL).
Fill Factor
http://msdn.microsoft.com/en-us/library/ms177459.aspx
fill factor Option
http://msdn.microsoft.com/en-us/library/ms191005.aspx
Reorganizing and Rebuilding Indexes
December 31, 2011 at 3:01 am
ESAT ERKEC (12/31/2011)
I have a lot of indexes i want to learn if I need shirink fill factor 80 to 95 to use data space...
You can rebuild the inexes to adjust the fill factor, however you may end up using MORE space due to excesive page splits. Fill factor is basically used to reserve space in each page for new records or modified variable length columns. So, if you set the the fill factor too high, you may experience a large amount of page splits. Let's have a simple example. Let's say that you have a table of 100 pages with 80 percent fill factor, and you adjust it to 95 percent ending up with 85 pages. If you experience page splits in 15 of these 85 pages, then you still end up with a 100 pages and you have no gain at all.
So, how much free space is there? With 80 percent fill factor, a "full" page will have (100-80)*8096/100 = 1619 free bytes. If you reduce this to 95 percent, then you have 404 bytes free in each page. As you, I often find that 80 percent fill factor is more than I need, but you should really do some calculation to estimate what fill factor you need.
December 31, 2011 at 5:07 am
We have a ERP system and that tables will not use 2012.
I can set this tables index fill factor 80 to 95 in 2012 I gain some space
I want to learn SQL SERVER can use this space or first drop index
then shirink and then recreate index and than sql use this space
December 31, 2011 at 5:17 am
ESAT ERKEC (12/31/2011)
We have a ERP system and that tables will not use 2012.I can set this tables index fill factor 80 to 95 in 2012 I gain some space
I want to learn SQL SERVER can use this space or first drop index
then shirink and then recreate index and than sql use this space
IMO for ERP System, fill factor 80 is not bad. If you have space issues, you can look for some other alternatives. Fill factor setting is not for saving disk space.
December 31, 2011 at 10:33 am
Dev (12/31/2011)
ESAT ERKEC (12/31/2011)
We have a ERP system and that tables will not use 2012.I can set this tables index fill factor 80 to 95 in 2012 I gain some space
I want to learn SQL SERVER can use this space or first drop index
then shirink and then recreate index and than sql use this space
IMO for ERP System, fill factor 80 is not bad. If you have space issues, you can look for some other alternatives. Fill factor setting is not for saving disk space.
In my current ERP I use 85% as default and then a few tables are between 70% and 80%. The net change was minimal on yearly growth but page splits are pretty much completely gone. I went from rebuilding all indexes weekly to not even quarterly on most tables.
December 31, 2011 at 10:34 am
okbangas (12/31/2011)
Og no changes are being made to those tables, I would use full factor 100%.
+1
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply