Fill factor

Fill Factor is telling how much space, from a page, can be used with storing records.
It’s possible only for indexes.
100% = 0% = to use the hole page = 8kbytes.
Having a different fill factor then 100%, it means you preserve some free space.

The fill factor is “activated” when you create an index, rebuild an index, reorg an index.
When you do create, rebuild, you can set a different value for the fill factor
When you do reorg, the value of the fill , it’s the used/default value.

 

Depending of your scenariou you can have a 100% or less fill factor:

• if it’s doing inserts at the end of the index (with a identity clustered column ) and no update of a varchar data type columns = then use 100% fill factor
• if it’s doing random insert (like GUID as cluster key column) or doing update of a variable data type length = use a different , a low fill factor. This should be monitor (1 per week or per 2weeks or 1 per month) to see if you still have a high number of page split. If yes, reduce again the fill factor with 5% (or 10%) and re-monitor it.

 

Having a lower fill factor, combine with your scenariu , you will have:
PRO:

• less page splits

CONS

• additional storage
• additional pages to be read

Example:

We are using two tables , same fields , but one will have fill_factor = 100, one will have fill_factor =60.

drop table if exists dbo.fillFact_Full;
create table dbo.fillFact_Full
(
id int not null,
myChar char(985) not null,
myVarChar varchar(500) null
)
GO
CREATE UNIQUE CLUSTERED INDEX idx_fillFact_Full_id ON dbo.fillFact_Full
(id);
GO
drop table if exists dbo.fillFact_60;
create table dbo.fillFact_60
(
id int not null,
myChar char(985) not null,
myVarChar varchar(500) null
)
GO
CREATE UNIQUE CLUSTERED INDEX idx_fillFact_60_id ON dbo.fillFact_60
(id);
GO

Initial setup, insert 20 records, and the two tables will be identical.

Now, activate the fill factor  = 60 for table 2, “fillFact_60” , and fill factor = 100 for the table “fillFact_FULL”, and look at the space and pages used:

 

So, definitely , the space used is much more for the one with fill factor = 60

What about pages read, when doing a select (more then 1 record, like select *)

Again, more logical reads for the one with fill factor = 60.

 

Let’s look at the location of each record in pages ; for this will use undocumented function : %%physloc%% and sys.fn_PhysLocCracker(%%physloc%%)

 

And now let’s update some records, or insert in a particular page, base on key clustered value.

–update some records with id = 9 ,12
–for id = 9,12 , page = 392
update dbo.fillFact_Full
set myVarChar = LEFT(REPLICATE(‘update:’ + CAST(id AS VARCHAR(50)) ,500),500)
where id IN( 9 ,12)

–for id = 9,12 , page = 400
update dbo.fillFact_60
set myVarChar = LEFT(REPLICATE(‘update:’ + CAST(id AS VARCHAR(50)) ,500),500)
where id IN( 9 ,12)

–see the location of each record:
select flc.*,f.* from dbo.fillFact_Full as f CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) as flc
select flc.*,f.* from dbo.fillFact_60 as f CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)as flc

 

We can see, that in table with fill factor = 100 , one record with id = 9 was able to store the additional information, but for the record with id = 12 ,no; it needed a new page , pageid = 465.Additional , it’s moving the half of records, from id = 12 till 24.

For the table with fill factor = 60, both update were able to be stored in the same page. page id = 400

 

For insert , the same : adding some records will have the same effect as update.

code can be found here

S

 

 

 

 

 

Page Split – monitor

To monitor page splits, we can :

• Extended events : sqlserver.transaction_log
• Look in transaction log  with fn_dblog = LOP_DELETE_SPLIT = this will be showing the bad page split.
• performance counters  –  ‘Page Splits/sec’ = this is holding good splits + bad splits

SELECT

     COUNT(*) AS countOfSplits

    , AllocUnitName as idxName

FROM

     fn_dblog(NULL,NULL)

WHERE

     Operation = ‘LOP_DELETE_SPLIT’

GROUP BY

     AllocUnitName

ORDER BY

    countOfSplits DESC

S

 

 

Page Split

Page split occurs when there is not enough space in a particular data page to insert/update a specific record. There are 3 ways that generate Page splits:

• End/Sequential/Identity Insert  = good page split = when we insert at the end of the table . ex: imagine you have an identity field  which is also PK and clustered index , and all new records would have id+1
• Mid lvl table Inserts = bad page split
• Updates = bad page split = when we’re updating a record on a page that don’t have enough space to accommodate the extra space. For this needs extra space and voila , page split

For bad page split, this is bad because there is additional information that needs to be save in the transaction log.

• adding a new page
• move half of records to the new page (insert into the new page and delete from old page  = mark it as delete )
• update link pointers in the pages

 

Here is a sample of it:

We have a simple table , with id field clustered :

–create a table with an id and some string , so that 2 records will be stored in a page
drop table if exists dbo.pageSplit;
create table dbo.pageSplit
(
id int not null,
myChar char(3500) not null,
constraint pk_pageSplit_id PRIMARY KEY CLUSTERED(id)
)
go

Now ,let start inserting one record, starting with value of id = 3 , and see the log data generated.

begin transaction
insert into dbo.pageSplit(id,myChar)
values(3,REPLICATE(‘3’,3500));

select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID(‘db_workspace’)
–3796
commit transaction

 

Log data generated

 

Next step, insert value 2:

begin transaction
insert into dbo.pageSplit(id,myChar)
values(2,REPLICATE(‘2’,3500));

select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID(‘db_workspace’)
–3756
commit transaction

 

Step 3, insert a record with value 1.

begin transaction
insert into dbo.pageSplit(id,myChar)
values(1,REPLICATE(‘1’,3500));

select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID(‘db_workspace’)
–5784
commit transaction

Log data generated is 5784, almost double (70%) .wow…

You can monitor it with perform counters:

SQLServer:AccessMethods:Page Splits/Sec

Or from dmvs:

select cntr_value
from sys.dm_os_performance_counters
where counter_name = ‘Page Splits/sec’

Code here

S