Index fill factor and page splits

We all know that Indexes have an option of specifying fill-factor. By fill-factor we mean that we can specify how much of that page to be filled up with data. By specifying a fill-factor of 80 %, we intend to free 20 % of the space for future additions to the tables. Why do we do that? A correctly chosen fill-factor value can reduce potential page splits by providing enough space for index expansion as data is added to the underlying table.

Now does SQL Server always maintain the fill-factor? No, Database Engine does not dynamically maintain the free space on the pages. Trying to maintain that, can cause page splits to happen and would defeat the purpose of fill factor. fill factor will only be enforced during creation and rebuild of the indexes.

Let us see how this work through a small example. For that, first I am creating a table and a clustered index.

create table pagesplit (c1 int primary key,c2 char (1000))

create clustered IX_C1 on pagesplit(c1) with fillfactor =80

Now I fill the table with some rows.

insert into pagesplit values (1,’ONE’)

insert into pagesplit values (2,’TWO’)

insert into pagesplit values (3,’THREE’)

insert into pagesplit values (4,’FOUR’)

insert into pagesplit values (5,’FIVE’)

insert into pagesplit values (6,’SIX’)

insert into pagesplit values (7,’SEVEN’)

I have inserted 7 rows into the table

Now I run the undocumented DBCC Command

DBCC IND (‘PRACTICALS’,PAGESPLIT,1)

For a full description of the output DBCC IND gives you can refer the blog by Paul Randal here

Now from this output I can see that there is one page 2218 which is a data page.

Let us see DBCC Page output for the page 2218

DBCC PAGE (PRACTICALS,1,2218,3)

Note: Before you run DBCC Page you need to turn on trace flag 3604 by running DBCC traceon (3604,-1)

PAGE: (1:2218)

BUFFER:

BUF @0x03DF7800

bpage = 0x0D2B0000                   bhash = 0x00000000                   bpageno = (1:2218)

bdbid = 7                            breferences = 0                      bcputicks = 0

bsampleCount = 0                     bUse1 = 1557                         bstat = 0xc0000b

blog = 0xbbbbbbbb                    bnext = 0x00000000

PAGE HEADER:

Page @0x0D2B0000

m_pageId = (1:2218)                  m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000

m_objId (AllocUnitId.idObj) = 130    m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594046447616

Metadata: PartitionId = 72057594045923328                                 Metadata: IndexId = 1

Metadata: ObjectId = 373576369       m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 1008                       m_slotCnt = 7                        m_freeCnt = 1005

m_freeData = 7173                    m_reservedCnt = 0                    m_lsn = (955:184:2)

m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0

m_tornBits = 0

For a full description of DBCC Page output, refer Paul Randall’s blog here

Note: The m_freecnt gives you the free space available on the page.

So the m_freecnt value here means that we have 1005 bytes of free space in the page.

At this point, fill factor is not playing any role here. I will now run re-index command on the Index IX_C1 different fill factor value and see how a change in fill factor value changes the page structures.

ALTER INDEX IX_C1 ON PAGESPLIT rebuild with (online = on,FILLFACTOR = 70)

Once after I run this command I run DBCC IND to get the new page details.

DBCC IND (‘PRACTICALS’,PAGESPLIT,1)

Now I get two pages with PagePID 2226 and 2231.

Running DBCC PAGE (PRACTICALS,1,2226,3) gives me the below output.

PAGE: (1:2226)

BUFFER:

BUF @0x03D8F580

bpage = 0x0AFF8000                   bhash = 0x00000000                   bpageno = (1:2226)

bdbid = 7                            breferences = 0                      bcputicks = 0

bsampleCount = 0                     bUse1 = 2466                         bstat = 0xc0000b

blog = 0xbbbbbbbb                    bnext = 0x00000000

PAGE HEADER:

Page @0x0AFF8000

m_pageId = (1:2226)                  m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x0

m_objId (AllocUnitId.idObj) = 135    m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594046775296

Metadata: PartitionId = 72057594046251008                                 Metadata: IndexId = 1

Metadata: ObjectId = 373576369       m_prevPage = (0:0)                   m_nextPage = (1:2231)

pminlen = 1008                       m_slotCnt = 6                        m_freeCnt = 2018

m_freeData = 6162                    m_reservedCnt = 0                    m_lsn = (955:291:60)

m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0

m_tornBits = 0

Here the m_freeCnt value is 2018 as opposed to 1005.

Now I will do re-indexing with a fill factor of 50 % and see how the page changes.

ALTER INDEX IX_C1 ON PAGESPLIT rebuild with (online = on,FILLFACTOR = 50)

DBCC Page output of the corresponding pages DBCC IND output gives me, tells that the m_freeCnt for first page is 4044 and that of second page is 5057 bytes, which means that after re-indexing it maintains ~50% free space.

So always keep a proper fill factor and do have a proper re-indexing strategy so that you have enough free space for future expansion and page splits are avoided.
Special Thanks to Paul Randal (Blog|Twitter )for helping me understand the concept.

One thought on “Index fill factor and page splits

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s