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.

3) Lower fragmentation due to Fill Factor can improve overall performance.

If your inserts and updates are faster, that helps remove locks. Now that they don't cause fragmentation, reads are in line more. There are some pretty heavy benefits.

4) Maintenance of Fragmentation will decrease.

If you fragment less often by having a lower fill factor, you don't have to rebuild or reorganize quite as often. This can help you a lot if you cannot rebuild your indexes online.

These are things I've found so far. A noticeable key word on the positives is "Appropriate".

Please take this with a grain of salt and sanity. As always, all advice depends on the environment. This is just something to think about.