Will FILLFACTOR = 99 slow down my SQL Server?

Question

I have read this today:

Closely related to index rebuilding is the fillfactor. When you create a new index, or rebuild an existing index, you can specify a fillfactor, which is the amount the data pages in the index are filled when they are created. A fillfactor of 100 means that each index page is 100% full, a fillfactor of 50% means each index page is 50% full.

If you create a clustered index (on a non-monotonically ascending column) that has a fillfactor of 100, that means that each time a record is inserted (or perhaps updated), page splits will occur because there is no room for the data in the existing pages. Numerous page splits can slow down SQL Server’s performance.

I have noticed that we have a database in our company with a table Department with departmentName as varchar(50) not null. We can have around 100 departments in this table.

We also have a non clustered index as follows:

CREATE NONCLUSTERED INDEX [DEPARTMENT_NAME]
    ON [dbo].[Department]([departmentName] ASC) 
    WITH (FILLFACTOR = 99, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, 
          PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
          IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, 
          ONLINE = OFF, MAXDOP = 0);

So, I am wandering if this FILLFACTOR = 99 would slow down our database. What does the author mean with "page splits occur when there is no room for data"?

Answer 1

Fill factor is critical in fragmentation

What does the author mean with "page splits occur when there is no room for data"?

There are basically two types of splits good page split or sequential page split which occurs when leaf level of index page is full and when new record is inserted page split occurs which results in allocation of new page and data is written in sequential order because new page is added at last of all pages.

The other one is bad page split or non sequential page split which occurs due to insert or update operation on a page resulting in page split in between. What happens is when record is updated a space for new record is created and this might splits page and move some of record on new page now this new page would be just after page that was updated hence severely creating a mismatch in order by which Logical clustering keys are arranged and how data pages are arranged. In fact depending on how fast new page can be allocated for page split it can also cause performance issue. Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. This article aptly demonstrates how page split can occur. You can also read this article by Paul about how to see bad page splits. Both of these use undocumented commands if you have SQL Server 2012 and above you can use Extended events trace to track nasty page splits

One more drawback associated is when huge number of page splits occur they could saturate I/O by utilizing it. Consider a scenario where update causes page split now one I/O would be required for update to index key one for update to page(normally) it would also require additional I/O for new page addition and updating index after new page is created, on a busy system with not so upto date hardware this could cause performance issue

There is other way using DMV to find number of page splits happening in database but it wont tell you which one is good and which one is bad

--Script to check page split for index
SELECT
IOS.INDEX_ID,
O.NAME AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX,
IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS
JOIN
SYS.INDEXES I
ON
IOS.INDEX_ID=I.INDEX_ID
JOIN
SYS.OBJECTS O
ON
IOS.OBJECT_ID=O.OBJECT_ID
WHERE O.TYPE_DESC='USER_TABLE'

Please let me know if you need further explanation