Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

The tag has no usage guidance.

1
vote
1answer
27 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... ║ ╠══════════╬═════╬══════╬════════════...
0
votes
2answers
44 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....
0
votes
2answers
254 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 ...
3
votes
4answers
819 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 ...
7
votes
1answer
389 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 ...
6
votes
2answers
2k 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 ...
2
votes
2answers
169 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 ...
1
vote
1answer
85 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 ...
3
votes
1answer
104 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 ...
2
votes
1answer
635 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, ...
4
votes
2answers
2k 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....
0
votes
2answers
334 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 ...
4
votes
1answer
1k 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 ...
2
votes
1answer
444 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 ...
2
votes
0answers
85 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 ...
2
votes
2answers
368 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 ...
4
votes
1answer
871 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 ...
0
votes
1answer
73 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 ...
3
votes
2answers
212 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 ...
4
votes
1answer
236 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 ...
4
votes
1answer
794 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 ...
8
votes
2answers
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 ...
0
votes
1answer
383 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 ...
1
vote
1answer
418 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 ...
3
votes
1answer
4k 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 ...
9
votes
1answer
1k 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 ...
3
votes
2answers
292 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? ...
13
votes
1answer
1k 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 ...
9
votes
2answers
1k views

How much “Padding” do I put on my indexes?

How do you know how much "Padding" an index should have?