Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fill Factor and Backup Compression Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 12:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:40 AM
Points: 24, Visits: 317
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?
Post #1398210
Posted Wednesday, December 19, 2012 8:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 373, Visits: 1,234
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.
Post #1398468
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse