Fill Factor and Backup Compression

  • How does the Index Fill Factor influence the size of a compressed backup? Is the fill factor space that is empty ignored during a compressed backup?

  • shindle 17293 (12/19/2012)


    How does the Index Fill Factor influence the size of a compressed backup? Is the fill factor space that is empty ignored during a compressed backup?

    Yes,

    I do believe it's ignored.

    SQL backup algorithm backups data only. Any empty space or page is or will be ignored. That's part of the reason why backups are smaller, even without compression. They also start moving pages without specific order so the initial backup can take less time to finish.

    The question would be, do you really need a different FILL FACTOR for you Index? So think in terms of performance. For instance... if you have a big table with a Cluster Index on it and its column is being updated or inserted frequently, you may start changing or using a different FILL FACTOR, as the new inserted data won't cause as many page splits or fragmentation if you use the default one; you may even consider to use a different column as CI, but that requires further analysis and may or may not be suitable for your table or environment.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply