Data Compression and Fill Factor

Data Compression and Fill Factor

Rate This
  • Comments 2

Recently, I was asked this question "If I have a compressed index and I rebuild it with fill factor 70%, do I actually get any fill factor?  Or does compression take away the empty space?". The answer to this question is 'yes'. In fact all index options listed below available with data compression (both for ROW and PAGE)


<relational_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
Here is a simple script to show the 'fill factor" experiment
create table t1 (c1 int, c2 char (1000))
go

-- load 10000 rows
declare @i int
select @i = 0
while (@i < 10000)
begin
insert into t1 values (@i, REPLICATE ('1', 500))
set @i = @i + 1
end

-- create index with 40% fill factor
create clustered index ci on t1 (c1) with (fillfactor = 40)


-- run this DMV query to get the info on fill factor, number of pages
select max_record_size_in_bytes,page_count, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats (db_id('foo'),   object_id('foo.t1'),    null, null, 'DETAILED')
where object_name (object_id) like 't1'

max_record_size_in_bytes                page_count           avg_page_space_used_in_percent
------------------------                 --------------------         ------------------------------
1011                                        2500                           50.0370644922165


-- enable ROW compression.Note, it will maintain the fill factor or 40%
alter index ci on t1 rebuild 
 with (
    data_compression = ROW)

-- The output below shows almost ½ the page couns but fill factor is around 40%

max_record_size_in_bytes                 page_count           avg_page_space_used_in_percent
------------------------                 --------------------         -----------------------------
511                                               1430                 44.2960588089943


-- enable PAGE compression. Note, it will maintain the fill factor or 40%
alter index ci on t1 rebuild 
 with (
    data_compression = PAGE)

max_record_size_in_bytes                      page_count           avg_page_space_used_in_percent
------------------------                          --------------------          ------------------------------
9                                                             42                   38.6835680751174

Thanks
Sunil Agarwal

Leave a Comment
  • Please add 8 and 7 and type the answer here:
  • Post
  • Nice post.. for those tables which are compressed maybe even a slightly higher fillfactor will enhance the compression benefits (especially for the Page level) to reduce # pages and still be less prone to page splits as compressed new rows or updated rows would not cause a Page split until more data is added then with uncompressed data changes

    I also like to size the fillfactor based on % growth rate / row size factors to reduce # Page splits that would occur with say a more default FF of say 80%

  • I was testing in this area and post was helpful

Page 1 of 1 (2 items)