August 19, 2001 at 9:32 pm
Hi all
I am after some general recommendations re fill-factor. By default it is zero, which seems strange to me and a waste of space at my leaf entries. I am running a OLTP database, around 200 users, and data is inserted on a regular basis for a variety of the 153 total tables. Most a reference tables with little movement, but others are more day-to-day business transaction tables with heavy usage. Anyhow, I am planning on a 40% fillfactor, I was going to opt for 50% but took the extra 10% off more for "saftey sake". I am no confident with my approach (ie. guessing)... and am after some thoughts regarding performance and how to come up with a more scientific approach to determining a good "default" value for OLTP based apps.
I understand that a higher fill factor will possibly decrease read performance with more jumps on scans etc. This worried me somewhat as even OLTP based apps have a good 60%+ read vs write. And this from the BOL..
"Note Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can degrade database read performance by an amount inversely proportional to the fill factor setting. For example, a fill factor value of 50 percent can cause database read performance to degrade by two times."
Ideas/thoughts?
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 20, 2001 at 12:13 pm
I think you need to monitor the page splitting. When you have the low fillfactor, if the insert occurs in the middle, then there can be a performance issue due to the overhead of page splitting. I have had to worry about this in the past and found a 50% fillfactor improved performance. Haven't looked at it in SQL 2000 though.
Steve Jones
August 20, 2001 at 4:29 pm
An alternative is to go with a higher fill, but rebuild more frequently. It also depends a lot on what your primarykey is - if you're using something that always adds to the end (identity!), fill factor doesnt matter as much.
Andy
August 20, 2001 at 6:33 pm
Thanks guys, I will monitor page splits and go from there. I rebuild indexes every sunday at 11pm. A lot of tables have their own natural keys rather than using identity but funny enough the most heaviest used tables all use identity.
Strange that BOL bascially warn you totally off the use of fillfactor and dont mention how to proactivly monitor it to determine a good base line for altering the factor.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 21, 2001 at 4:50 am
Its definitely necessary sometimes to dig into this stuff, but it only matters when you're tight on resources. I learned this the hard way - just buy a bigger dog! Combine that with decent schema design, SQL just works.
Andy
August 27, 2001 at 9:26 pm
Just for your information, the default fill factor of 0 is the same as a fill factor of 100. If you use the default of 0 the pages in the leaf level will be nearly full.
May 1, 2002 at 2:53 am
Hi
I am confused with the real meaning behind fill-factor zero, a lot of MS docs state its an "internally" managed factor that will work in nearly all cases. I am not sure if it really IS equivalent to 100. Any thoughts on this or studies done as to what SQLServer is going with a value of zero. In the end, I would rather leave the default and have no or little admin issues, rather than set a value only to spend hours re-tuning and investigating. Ive learnt lessons many times in the oracle world playing with settings only to back fire on me and cost more time in subsequent admin.
Thoughts?
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 1, 2002 at 4:56 am
The key to a fill factor is in the page splits, but keep in mind that the fill factor never resets itself once built in the begining. Meaning if I started with 70% then the pages are filled up to 70% when built but do not remain that way. They will fill up to 100% and a page split will occurr to push data into the next page. This is why on some systems with high volume and indexes you may want to rebuild occasionally. Also consider the insertion point of your data. Exampl I have a table that adds 1000+ rows a day but I have a 0 fill factor since the value is based on a datetime and will always insert at the end of the table. Thus a fill factor is just wasted space that will never be used. Also I use 0 on all static reference tables, those that have changes but never add rows, and those that will always be less than a few MBs (a few 8k pages) in size as they don't pose enough growth fast enough to cause a page split issue. Other instances I weigh space constrainst against page splits and growth based weekly or monthly. If a table grows rapidly and does not fit any of my previous scenarios I use fill factor to limit the number of page splits to utilize resources as best as possible and prevent as much HD access contention as possible due to data movement accross pages.
I rebuild my indexes and reset most of my fill factors once a week as the pages become full some once a month and some as needed. I know 2000 offers a INDEX DEFRAG which helps keep the index logically ordered and based on BOL
quote:
DBCC INDEXDEFRAG also compacts the pages of an index, taking into account the FILLFACTOR specified when the index was created
Now as for this
quote:
"Note Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can degrade database read performance by an amount inversely proportional to the fill factor setting. For example, a fill factor value of 50 percent can cause database read performance to degrade by two times."
Consider when SQL is accessing data the fastest and most efficient way to get it is to make as few extent switches and page reads as possible as you are having to open that 8k page to retrieve the data. The lower the fill factor the more pages the data lives the longer it will take to read the data out due to switching from one page to another. This is more so for serveral different non-range items being pulled, then a range of items is next in being adversely effected, and finally one row selection is about the leat effected. Then you have to tae into account how the data is accessed. I a table scan then you read most all pages, if an index scan then you read fewer pages, and finally an index seek is even fewer pages. So you really are consider the read based on those two factors. Here is an example to show the difference in 0 and 50.
--Run this to check your tables out.
DECLARE @id int, @indid int
SET @id = OBJECT_ID('tbl_EventData') --TableNameHere
SELECT @indid = indid
FROM sysindexes
WHERE id = @id
AND name = 'PK_tbl_EventData' --IndexNameHere
DBCC SHOWCONTIG (@id, @indid)
GO
Example --I use the PK which is clustered
/*This was with the fill factor of 0*/
DBCC SHOWCONTIG scanning 'tbl_EventData' table...
Table: 'tbl_EventData' (779149821); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 6412
- Extents Scanned..............................: 810
- Extent Switches..............................: 809
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.01% [802:810]
- Logical Scan Fragmentation ..................: 0.02%
- Extent Scan Fragmentation ...................: 0.25%
- Avg. Bytes Free per Page.....................: 20.6
- Avg. Page Density (full).....................: 99.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
/*This is a snap with 50%*/
DBCC SHOWCONTIG scanning 'tbl_EventData' table...
Table: 'tbl_EventData' (779149821); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 12732
- Extents Scanned..............................: 1599
- Extent Switches..............................: 1598
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.56% [1592:1599]
- Logical Scan Fragmentation ..................: 3.08%
- Extent Scan Fragmentation ...................: 0.25%
- Avg. Bytes Free per Page.....................: 4029.1
- Avg. Page Density (full).....................: 50.22%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Based on this I will have a lot mor work to do to read from the database or even update as the record has to be found read ad altered. Inserts into early parts of the database though will be faster and page splits will be limited.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply