<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Brian Ellul
»
The FILL FACTOR
16 posts, Page 1 of 2
1
2
»»
The FILL FACTOR
Rate Topic
Display Mode
Topic Options
Author
Message
brian118
brian118
Posted Wednesday, December 15, 2010 9:44 PM
SSC Veteran
Group: General Forum Members
Last Login: Friday, April 26, 2013 2:31 AM
Points: 228,
Visits: 495
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
faelconn1ck
faelconn1ck
Posted Wednesday, December 15, 2010 9:46 PM
Forum 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
The Grateful DBA
The Grateful DBA
Posted Thursday, December 16, 2010 7:29 AM
Grasshopper
Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 12,
Visits: 375
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
jts_2003
jts_2003
Posted Thursday, December 16, 2010 7:44 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 6:12 PM
Points: 1,060,
Visits: 4,169
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
jxhopper
jxhopper
Posted Thursday, December 16, 2010 7:50 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:23 PM
Points: 6,
Visits: 196
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
Nadrek
Nadrek
Posted Thursday, December 16, 2010 8:40 AM
Say Hey Kid
Group: General Forum Members
Last Login: Yesterday @ 12:28 PM
Points: 675,
Visits: 2,031
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
brian118
brian118
Posted Thursday, December 16, 2010 8:48 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, April 26, 2013 2:31 AM
Points: 228,
Visits: 495
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
Mattrick
Mattrick
Posted Thursday, December 16, 2010 10:19 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Yesterday @ 6:47 AM
Points: 581,
Visits: 694
Brian, thank you for the great article. It has shed some light on a very interesting topic.
Post #1035959
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Thursday, December 16, 2010 10:59 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 6:14 PM
Points: 31,421,
Visits: 13,734
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
alen teplitsky
alen teplitsky
Posted Thursday, December 16, 2010 1:10 PM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 2:11 PM
Points: 1,409,
Visits: 4,506
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 »
16 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
