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"?