Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The FILL FACTOR Expand / Collapse
Author
Message
Posted Wednesday, December 15, 2010 9:44 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 6:51 AM
Points: 244, Visits: 543
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 -
Post #1035636
Posted Wednesday, December 15, 2010 9:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 16, 2010 6:47 PM
Points: 2, Visits: 7
What's the consequences if wrong fill factor has been set. So called poor performance will cause something like deadlock or query timeout?
Post #1035637
Posted Thursday, December 16, 2010 7:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:02 AM
Points: 13, Visits: 594
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)
Post #1035831
Posted Thursday, December 16, 2010 7:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 2:15 AM
Points: 1,110, Visits: 4,907
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?
Post #1035844
Posted Thursday, December 16, 2010 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:17 PM
Points: 8, Visits: 244
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.
Post #1035847
Posted Thursday, December 16, 2010 8:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:06 PM
Points: 887, Visits: 2,453
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).
Post #1035893
Posted Thursday, December 16, 2010 8:48 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 6:51 AM
Points: 244, Visits: 543
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 -
Post #1035901
Posted Thursday, December 16, 2010 10:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:32 PM
Points: 581, Visits: 733
Brian, thank you for the great article. It has shed some light on a very interesting topic.

Post #1035959
Posted Thursday, December 16, 2010 10:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:55 PM
Points: 31,278, Visits: 15,736
Good article, Brian. It's a nice explanation of what Fillfactor does and some ideas on how to set it.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1035993
Posted Thursday, December 16, 2010 1:10 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 9:36 AM
Points: 1,414, Visits: 4,544
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1036083
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse