10 votes
Accepted

MS SQL Page split confusion

From Books Online (emphasis mine): FILLFACTOR =fillfactor Applies to: SQL Server 2008 through SQL Server 2016, SQL Database V12. Specifies a percentage that indicates how full the Database ...
Michael Green's user avatar
10 votes
Accepted

Is it okay to have a fill factor of 100 on the clustered index that is on an ID column?

In most cases you do want a FILLFACTOR of 100. Only in special cases would you want to lower that value. Updates only cause page-splits if they increase the size of the row such that it won't still ...
Solomon Rutzky's user avatar
7 votes

Can lowering fill factor result in more page splits?

When you have lots of page splits caused by random middle-of-BTree inserts, your pages will naturally average around 65%* full. Split pages start at 50% full and any that fill up past 100% get split ...
David Browne - Microsoft's user avatar
6 votes
Accepted

How is calculated fill-factor percentage related to size of database?

What you see is correct. When you rebuild an index first the new index is built and only then the old index is dropped. And if you don't use SORT_IN_TEMPDB = ON the space that server needs to make ...
sepupic's user avatar
  • 10.8k
5 votes
Accepted

Can Fill Factor be changed on specific tables without index rebuild?

This is not how FILLFACTOR works. It only applies to pages written during a CREATE or REBUILD operations. Pages are always filled as much as possible as they are created or split. As per the MSDN ...
Solomon Rutzky's user avatar
4 votes
Accepted

Can I change Fill Factor option for our database server?

As stated in the documentation "Fill factor 0 and 100 are actually interchangeable and fill factor does not apply to heaps, which are tables without a clustered index. Brent Ozar has pretty good ...
Spörri's user avatar
  • 4,704
4 votes

Is concept of fill factor useful only when index is rebuilt?

Fill factor is concept that works only when creating a new index or when the index is rebuilt. Fill factor is only applied when an index is created, rebuilt, or reorganized filling the page to the ...
Brendan McCaffrey's user avatar
4 votes

Fillfactor is ignored when non-clustered index added

Fillfactor can be set once over the server (so it will be used in all newly created indexes unless another FF is explicitly specified in CREATE INDEX statement) or it can be defined individually for ...
sepupic's user avatar
  • 10.8k
3 votes
Accepted

Does Sql Server maintain the free space defined by fillfactor?

Ni, it isn't maintained. It is there to postpone page splits. But after so many inserts, the page will split. Then it is up to you whether you fell like doing anything about it (rebuild or reorganize)....
Tibor Karaszi's user avatar
3 votes
Accepted

Can lowering fill factor result in more page splits?

It's really not possible that changing the fill factor would cause more page splits, assuming that you are measuring the page split rate immediately after a 100% fill factor rebuild/reorg vs. an 85% ...
Tony Hinkle's user avatar
  • 7,939
3 votes
Accepted

Big Amount of <free_space_in_bytes> in Buffer Pool's Database Pages

Is this a problem ? Maybe, maybe not. If your database(s) only make up 80% of your server's max server memory, it's about what I'd expect to see. If you have more data than RAM, then the free space ...
Erik Darling's user avatar
  • 40.6k
3 votes
Accepted

Fill Factor: Will the fill factor change automatically after it detects the changes in the index pages?

So, now, essentially the fill factor of the database is not 0 as there are two pages which are half full You are correct, but this is actually caused by the fill factor value. You cannot say that now ...
Shanky's user avatar
  • 19k
2 votes

Altering column from INT to BIG INT and fill factor trick

I have to tell you the only answer I can come up with is "This makes no sense what-so-ever". From what I can tell they were expanding the table out so there was room for the additional space for ...
Kenneth Fisher's user avatar
2 votes

Dealing with varying fill factors in a SQL Server 2014 environment

I'm not sure why you think those are the only options. If you look in IndexOptimize, the @FillFactor variable is passed in as NULL by default. The command that appends Fill Factor looks like this: ...
Erik Darling's user avatar
  • 40.6k
2 votes

How is calculated fill-factor percentage related to size of database?

How is calculated fill-factor related to the size of the database? This is due to the fact that the database is accumulated value of following and all their size counted/stored in pages. Heap ...
Shekar Kola's user avatar
  • 2,407
2 votes

Fill Factor for OLTP database

The answer is, unfortunately, it depends. Your database might be 70/30 write/read, but every table in the database won't be 70/30. Some tables, such as reference tables and lookups, may only be ...
HandyD's user avatar
  • 9,932
1 vote

Is there any benefit to specifying a fill factor when the table is empty?

It depends on if and how you defragment this index. (Which might not be something you want to do in the first place, but that is a different discussion.) The FF value doesn't affect the modifications ...
Tibor Karaszi's user avatar
1 vote

How is calculated fill-factor percentage related to size of database?

I have rebuilt all indexes in my database setting fill-factor of 95 (5% free space) This is almost always a bad idea. How many of those indexes use sequential primary keys? If any of them do, ...
James Jenkins's user avatar
1 vote

Fill Factor for OLTP database

I am setting up a new OLTP database and expect to write to the database about 70% of the time and read from it around 30% of the time. Given this criteria... This is NOT a criteria for ...
sepupic's user avatar
  • 10.8k
1 vote

Dealing with varying fill factors in a SQL Server 2014 environment

Complementing to Erik's answer, what I currently do is use CommandLog table to find patterns for endless index fragmentation and adjust it. If you are deploying index maintenance, then you can use ...
Kin Shah's user avatar
  • 62.2k
1 vote
Accepted

Fillfactor is ignored when non-clustered index added

First use permanenet table in this example.temp table show so many rows in this query "sys.dm_db_index_physical_stats" that it get confusing. Create table Test02 without fillfactor, populate and ...
KumarHarsh's user avatar
  • 1,628
1 vote

Can Fill Factor be changed on specific tables without index rebuild?

No it is not possible. FILLFACTOR is an option that can only be used with REBUILD or CREATE/DROP. Reference ALTER INDEX (Transact-SQL). <rebuild_index_option > ::= { PAD_INDEX = { ON | OFF }...
SqlWorldWide's user avatar
  • 13.1k
1 vote

Is it okay to have a fill factor of 100 on the clustered index that is on an ID column?

You will want to keep the default fill factor at 100 unless you have high fragmentation. Be aware that if the table is held entirely in memory your fill factor impact is greatly diminished. Don't ...
John's user avatar
  • 737
1 vote
Accepted

What should be the fill-factor for a table where only fixed-width columns will be updated?

PostgreSQL never does in-place updates of the data, so it doesn't matter whether the records are fixed width or not when deciding whether the fillfactor should be 100. It always has to find new space ...
jjanes's user avatar
  • 39.5k
1 vote

What should be the fill-factor for a table where only fixed-width columns will be updated?

Postgres doesn't store nulls directly in the field but rather keeps a bitmap of null values. So using 100 will be problematic. What you can do is use some default value such as 1/1/1970 just to ...
cohenjo's user avatar
  • 1,188

Only top scored, non community-wiki answers of a minimum length are eligible