Data Compression and Fill Factor

  • Article

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