Pages

26.11.13

Things I've Learned In Regards To Fill Factor:Quick Tips

I don't have much today... It's been a good week so far. I think I'm becoming a bit obsessed with this Fill Factor thing. This will be short... I swear. ^.^

Negatives to changing Fill Factor from 100 (also known as 0 )

1) Backups are bigger and take longer to run.

Unlike free space as in empty pages, empty space in the page still gets backed up. This will cause the time and size to increase.

2) Reads take longer.

When data is required, it reads the whole page. this does include the empty space. So if you could have fit the data in 10 pages vs 14. That's roughly 40% more reads.

3) More storage is required.

When a page isn't completely full, it takes more pages for the same data. That increases size on disk as well. If you're using SSD's that's something you can actually measure as a cost.

4) More RAM is required.

As previously mentioned, SQL Reads by the page. that means even the empty space is read into memory. Once there it can handle it just fine as far as processing, but the space is still used.

5) Maintenance of Fill Factors will increase.

This one is tough. I personally feel that you should maintain your Fill Factors anyways. Though most can live with the out of sight out of mind principle just fine. There are probably better places to target performance boosts first anyways.

Positives to changing Fill Factor to an appropriate number

1) Inserts and Updates can be quicker.

Now that you have free space in your pages, when you insert or update items that changes its place on the page, you can move it in faster without page splits.

2) Fragmentation can be lower.

Less page splits means less fragmentation. This can be a very good thing.