The FILL FACTOR

Question

The FILL FACTOR

brian118

Hall of Fame

Points: 3506
More actions
December 15, 2010 at 9:44 pm

#232528

Comments posted to this topic are about the item The FILL FACTOR
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

faelconn1ck Valued Member Points: 66 More actions · Answer 1

What's the consequences if wrong fill factor has been set. So called poor performance will cause something like deadlock or query timeout?

The Grateful DBA SSC Veteran Points: 263 More actions · Answer 2

Brian - Just wanted to mention how much I enjoyed the article. It's very well written and is a thorough introduction to the not-so-simple concept of how to set a FF to best impact your individual environment.

Keep up the great work...Troy (The Grateful DBA)

jts2013 Hall of Fame Points: 3226 More actions · Answer 3

Great article - I've always worried about Fill Factor as it does seem to be an area shrouded in confusion and contrasting views.

For example, if you're always adding to the end of a table/index, e.g. an increasing Order Number, I was told fill factor should be set to zero; this makes sense to me, but wondered if anyone agreed / disagreed, or had any other useful nuggets to do with Fill Factor? Perhaps we could start a Fill Factor FAQ?

Does anyone have any examples of where they have made changes to Fill Factor for a large table and seen noticeable performance improvements?

dbatech99 Mr or Mrs. 500 Points: 599 More actions · Answer 4

Good article. Nice explanation of how to evaluate read operations vs write operations to determine a good fillfactor.

I have typically used this method to set index fillfactor on a per case basis.

Nadrek SSC-Insane Points: 20039 More actions · Answer 5

I strongly suggest doing some tests with the following cases:

A) More than 10,000 pages of data!

B) A couple indexes, to watch behavior differences when SELECT, INSERT, UPDATE, and DELETE happens

B1) An index on a field whose INSERTs and UPDATEs tend to be sequential (say, current date/time)

B2) An index on a field whose INSERTs and UPDATEs tend to be arbitrary (say, date of birth, though SSN is an even better example

C) A third fillfactor example; between 85 and 95

D) Extra credit: Unique and non-unique

One not uncommon case for indexes in many systems is:

Millions of rows

Index on SSN-like field

DML operations are effectively on random SSN's, usually one at a time

Single row SELECTs on the SSN-like field are common

Bulk data SELECTs are perhaps uncommon, but certainly not rare (select SSN, col1, col2 order by SSN-like field)

Very, very quickly you'll find that a fillfactor in the high 90's results in a much smaller index than 100 would; likewise, it needs to be rebuilt/reorganized less often (also important if one has a small maintenance window).

brian118 Hall of Fame Points: 3506 More actions · Answer 6

faelconn1ck (12/15/2010)
What's the consequences if wrong fill factor has been set. So called poor performance will cause something like deadlock or query timeout?

With poor performance i understand slow Selects or DML statements, which eventually may also cause timeouts. I doubt that these may cause deadlock errors although indirectly they will since transactions will be open for a longer duration!

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

Mattrick Ten Centuries Points: 1176 More actions · Answer 7

Brian, thank you for the great article. It has shed some light on a very interesting topic.

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 8

Good article, Brian. It's a nice explanation of what Fillfactor does and some ideas on how to set it.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 9

faelconn1ck (12/15/2010)
What's the consequences if wrong fill factor has been set. So called poor performance will cause something like deadlock or query timeout?

depending on the size of the table and the amount of data selected an index seek can turn into a scan because you have to search through more pages

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 10

Really nice article, very well written. Thanks.

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Thanks for the article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dld SSC Eights! Points: 852 More actions · Answer 12

Thanks for the fine article. It raises a few questions for me.

- How is the optimizer affected by FF?

- If a FF of 50 is used, causing twice as many reads as a FF of 100, then could it cause the optimizer to not use an index?

- If, in this same scenario, the leaves eventually get more filled will the optimizer start using the index because less reads are required?

- Since I operate in a 24/7 environment, I have to try to minimize the amount of index rebuilding I do, since it locks the table for the duration of the rebuild and causes blocking for my users. I have a few fairly large tables (~10,000,000 rows, ~600,000 pages) that are heavily used for both reads/selects and DML. To minimize index rebuilding, would it make sense to go with a lower fill factor, knowing that would penalize the selects? In other words, does a lower fill factor keep an index from getting fragmented as quickly?

JottoMagic SSC Enthusiast Points: 103 More actions · Answer 13

Thanks for the great article.

We do index rebuilds every two weeks, and Fill Factor plays an important role.

I have found that the "magic number" for FF would be the amount of index space that needs to be available between index rebuilds. Our indexes grow approximately 4% per week (8% every two weeks), so I have a FF of 90%.

Theoretically, that keeps the system with 2% to 10% of free space in the index pages at any given time. From a general performance standpoint, you never want 100% page usage in a database that is written to. (This would be optimal in a read-only scenario.)

This is just the tip of the iceberg on a huge topic, and proper index maintenance can sometimes make or break the performance of a set of data.

Well, that's my two cents!

Happy Holidays!

-John

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 14

Thanks brian.

nice article...easily understandable!

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/