Fill Factor With Large Row Sizes
Published: 1st January 2014
This is just a little something to look out for when administrating a database as I’ve seen it catch a few people out.

Basically it’s all about making sure you have the correct fill factor for your indexes so that you don’t unnecessarily bloat your databases as a whole.

Now, at this point I would like to note that I realise fill factor is incredibly useful in reducing fragmentation within your indexes and tables and I would recommend that you utilise this feature where you can… the case I’m referring to is slightly different though… this is when your row sizes are large… in these cases extra care needs to be taken before you start to use the fill factor feature.

Now, to demonstrate this I’m going to use a simplified set of examples which means that my figures with regard to storage on a page won’t be 100% accurate, it’s simply to demonstrate the principle. In fact, just to emphasise that I’m going to claim a page is 8000 bytes (not the 8096 bytes that a real page has available for data).

Okay… having said that, let’s get on and look at a simple table…

create table dbo.smallRow
(
  
id int not null,
  
myDate datetime not null,
  
data char(68) not null,
  
constraint pk_smallRow primary key clustered(id)
)


We know that an int is 4 bytes, a datetime is 8 bytes, and the char is a fixed width 68 characters. That’s 80 bytes.

Therefore with simple maths (on my incorrectly sized data page) we can hold 100 rows on a data page.

In this case it’s a heavily inserted table and so we have an ideal scenario for a fill factor to be introduced… let’s say 80%. This would mean that we leave 20% of the page free for future inserts.

So, let’s use a simple example:

We have 24000 records… that would take 24000… at 100 records per page (zero fill factor) this would require 240 pages at a disk cost of 1.9MB.

If we have a fill factor of 80% we can hold 80 records per page… therefore the table now requires 300 pages but we have allocated ourselves a little buffer for future inserts to avoid fragmentation. The table now shows as 2.4MB. Not a massive increase… definitely an acceptable trade off against massive fragmentation.

Now, let’s look at a different table:

create table dbo.smallRow
(
  
id int not null,
  
myDate datetime not null,
  
data char(3988) not null,
  
constraint pk_smallRow primary key clustered(id)
)



Now you can see that this is virtually the same table structure… just with the massive size difference within the data column. So how does this affect things?

Well, a single record in the table is now 4000 bytes. Therefore we can now fit 2 records per page. So with our 24000 records this would require 12000 pages, which equates to 96MB.

What happens when we introduce a fill factor? Well if we were to place a fill factor of 80% on the table we suddenly end up in a world of trouble…

SQL Server has to leave 20% of the page free, but this means that we can only hold 1 record per page now. This is disastrous. We now require 192MB of space for the same data and we hardly have any resilience for inserts as we can only fit 1 new record on any page before that page would be full again.

Therefore in this case it’s better to leave the fill factor as 0 and to accept that page splits could happen during an insert rather than to double the size of your table on disk. Imagine the cost to disk if you had over a million records in this table etc. It soon adds up.

As mentioned at the start, I’m not against fill factor at all… I think it’s a VERY good feature and should be used widely… just make sure it’s also used wisely.
Comments:
NB: Comments will only appear once they have been moderated.