Questions tagged [fill-factor]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
3 votes
1 answer
173 views

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. Subsequently there will be fragmentation due to inserts/updates which will create new pages (page split) ...
variable's user avatar
  • 3,551
0 votes
1 answer
221 views

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

When the table has lots of data, and I create a new index with fill factor (say 30), then sql server will leave space for new entries to be added into the data page. This will keep fragmentation low ...
variable's user avatar
  • 3,551
0 votes
1 answer
41 views

Does Sql Server maintain the free space defined by fillfactor? [duplicate]

I'm new to concept of what fillfactor is and why it is used. So far, reading this article, I've learned that by setting the fillfactor configuration of an index, we specify how much space on each page ...
Shahryar Saljoughi's user avatar
1 vote
1 answer
133 views

Can I change Fill Factor option for our database server?

I have a production database for ERP which has clustered and non clustered indexes. Some other tables have index type as heap and i don't understand what does heap means. Every month i have a job run ...
Ayman Farouk's user avatar
5 votes
3 answers
937 views

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) using a maintenance plan. After the reindex the database is almost doubled in size - reported free space is 42%. ...
Sylwester Zarębski's user avatar
5 votes
4 answers
214 views

Can lowering fill factor result in more page splits? [closed]

We've been having a problem with page splits on a table that is a particular nuisance - its an audit log of activities in the database and has grown over 1TB. The main indexes are on the record type, ...
crucible's user avatar
  • 307
2 votes
1 answer
230 views

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

We were viewing sys.dm_os_buffer_descriptors using below query select d.[name] [Database_Name], (count(file_id) * 8) / 1024 [...
Aleksey Vitsko's user avatar
0 votes
2 answers
244 views

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, I'm wondering what would be good practice ...
equipe9's user avatar
  • 147
1 vote
1 answer
567 views

Does the Fill Factor setting affect heaps / heap tables with non-clustered indexes?

I came across this old article on Brent Ozar's blog (written by Kendra Little) regarding Fill Factor: https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/ In it, she briefly ...
J.D.'s user avatar
  • 37.2k
3 votes
1 answer
900 views

Choosing index fillfactor for large, highly inserted table

We have a table that will hold records on the 108 order. ╔══════════╦═════╦══════╦══════════════════════════╗ ║ id ║ a ║ b ║ ...many other columns... ║ ╠══════════╬═════╬══════╬════════════...
Alex's user avatar
  • 33
0 votes
2 answers
100 views

Dealing with varying fill factors in a SQL Server 2014 environment

I've recently become a DBA of sorts for our company's ERP system. I've been making small changes to the server & improvements have been steady. One area I'm concerned with is the index fill factor....
thatstevecena's user avatar
1 vote
2 answers
2k views

Fillfactor is ignored when non-clustered index added

I have a test scenario where I have added fill-factor = 90 to a clustered index over a random generated GUID primary key. If I create the table without fill-factor = 90 then the number of page ...
Tim's user avatar
  • 23
5 votes
4 answers
3k views

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

I've been digging around without any luck. SQL Server 2008: Is it possible to adjust the fill factor on a specific clustered index/table without having to rebuild the entire table? For example, if ...
Dave Sims's user avatar
  • 323
8 votes
1 answer
710 views

MS SQL Page split confusion

I am a bit confused about page splits in MS SQL and I'm looking for a definitive answer. There seem to be two versions of the story: 1 - Fillfactor only affects how full pages are at the time the ...
Mashchax's user avatar
  • 155
8 votes
2 answers
5k views

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

So I have a table with a clustered index on an ID column (which is an auto-increment column) in SQL Server. Is it okay to have a fill factor of 100 on this since the data will always get written with ...
croxfade's user avatar
  • 156
2 votes
2 answers
259 views

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

We have a table where we are planning to update timestamp fields only. In the first import these columns will be nulls then we will update them as the computation finished on these rows. The ...
KARASZI István's user avatar
1 vote
1 answer
97 views

Altering column from INT to BIG INT and fill factor trick

I have inherited a script that rebuilds the fill factor sitting at 90 fill factor on the INT primary key. It rebuilds it at a fill factor of 50. Then the script drops the index, alters the column to a ...
Silvey's user avatar
  • 11
3 votes
1 answer
271 views

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

So lets say that the default fill factor is left at 0 initially. Data has been loaded up into the server and one fine weekend, the index rebuild job is run. Now if it detects any new data which must ...
karun_r's user avatar
  • 339
2 votes
1 answer
1k views

Understanding index fillfactor

I'm trying to understand the index FILLFACTOR parameter on a simple example. I have a table that is generally updated only once in ten days, but the update is quite heavy (requires 5-6 hours). Now, ...
St.Antario's user avatar
  • 1,205
5 votes
2 answers
3k views

Performance hickups after Postgres 9.4 upgrade

After upgrading our database from 9.3.5 to 9.4.1 last night, the server suffers from high CPU spikes. The upgrade was done with pg_dump. So the database was converted to SQL and then imported into 9....
Andomar's user avatar
  • 3,495
0 votes
2 answers
702 views

Sql Server Fill Factor - Restart sql server services

I changed the Fill Factor option from 1 to 75. When I run a query that displays the fill Factor I am seeing 75 But I read online that I have to stop and start the sql Server services inorder for ...
user3325655's user avatar
4 votes
1 answer
3k views

Shrinking old partitions

I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have ...
m__'s user avatar
  • 529
2 votes
1 answer
792 views

Will FILLFACTOR = 99 slow down my SQL Server?

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 ...
Junior Mayhé's user avatar
2 votes
0 answers
91 views

work/cache table issues, heavy inserts with heavy reads

we have a work table that stores availability of products. This table is updated with millions of inserts every night and medium amounts of inserts throughout the day. during the updates, we delete ...
ttomsen's user avatar
  • 289
2 votes
2 answers
1k views

fragmentation for non cluster index growth very fast to 30

i have table with about 7 milion records.index fragmentation was about 90 . i try rebuild index for all index. and it works fine but after nightly job fragmentation for one of none cluster index ...
abianari's user avatar
  • 188
7 votes
1 answer
2k views

PostgreSQL: When should I use fillfactor < 100 for tables on SSD?

PostgreSQL documentation says that choosing smaller fillfactor lets table reserve space for future updates on the same page and "This gives UPDATE a chance to place the updated copy of a row on the ...
spatar's user avatar
  • 173
0 votes
1 answer
84 views

Is there a quantitative method to follow to determine FILLFACTOR for NONCLUSTERED INDEXes?

I've read different ideas to set FILLFACTOR and then monitor fragmentation over time (#1: 5 Things About Fillfactor, #2: Database Maintenance Best Practices Part II – Setting FILLFACTOR). Instead of ...
Adrian Torrie's user avatar
3 votes
2 answers
332 views

Fill Factor Setting and Performance

From what I've read, typically for indexing the recommendation is to leave the fill factor at 70-80, and the logic behind this seems that new data may be inserted into a table between existing values ...
user123's user avatar
  • 203
4 votes
1 answer
284 views

Fill factor based on index ranges

I'm designing a Postgres database for an events app. The app lists events sorted by when they start. Initially the app displays only 30 events. As users scroll through the list of events, more ...
Mark Schneider's user avatar
4 votes
1 answer
959 views

How can I alter the fill factor of an index outside of an ALTER INDEX command?

I am using a COTS package to perform index rebuilds across my SQL Server portfolio. The package unfortunately doesn't have an option to set index fill factors globally for an individual instance or DB ...
MattyZDBA's user avatar
  • 1,933
11 votes
2 answers
2k views

What fillfactor for caching table?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
Michal's user avatar
  • 163
0 votes
1 answer
556 views

Fill factor for later bigserial + index

I'm planning to import and update a huge table (3 Bil. rows) in the following step: create table with fill factor x import the data from csv files (6 files, ~21GB each) add a BIGSERIAL column create ...
Thi Duong Nguyen's user avatar
1 vote
1 answer
444 views

Will a composite Primary Key use the 15/16 page fill factor or the 1/2?

I understand that if a PK is a consecutive number, it will fill a page as its maximum which is a factor of 15/16. If it is a random PK the page fill factor will be of 1/2. I was wondering if my PK ...
Spredzy's user avatar
  • 2,248
3 votes
1 answer
5k views

How can I set the index fill factor back to server default?

I have some indexes that I was playing with on my dev server. I set the fill factor of these indexes using the following command: alter index all on dbo.Table rebuild with (fillfactor=80) That sets ...
DForck42's user avatar
  • 3,038
9 votes
1 answer
2k views

Why does the fill factor in SQL Server default to 0 (100%)?

I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95? Is ...
NTDLS's user avatar
  • 405
4 votes
2 answers
388 views

What is the signifigance of a fillfactor of 20?

I have a database for our ticket system in SQL Server express. I was poking around and noticed that a lot of the indexes have a fillfactor set to 20. What is the signifigance of it being set to 20? ...
DForck42's user avatar
  • 3,038
14 votes
1 answer
2k views

Behavior of data in indexes based on fill factor

Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%? From my understanding, when the data is inserted there ...
DForck42's user avatar
  • 3,038
9 votes
2 answers
1k views

How much "Padding" do I put on my indexes?

How do you know how much "Padding" an index should have?
Beth Lang's user avatar
  • 952