Fill Factor

Last Post 02 Jul 2009 10:30 AM by BMWGuy. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
BMWGuy
New Member
New Member

--
29 Jun 2009 10:29 AM
Here is what I understand about Fill Factor. The default for SQL2005 is 0 (or 100, same thing), and that means the whole leaf page will get filled before data gets written to the next page.

If you specify another number for fill factor, say 80, the page will get 80% full and then start writing on the next page...to avoid page splits.

So is it better to fill a whole page and have possibly alot of page splits, or specify 80?
BMWGuy
New Member
New Member

--
30 Jun 2009 04:47 AM
We are rebuiliding indexes every 2 weeks. In this time they become very fragmented and performance suffers.

After the index rebuilds, performance is much better, but degrades slowly (or not so slowly) until the next rebuild.

Today we are using 90 for our Fill Factor.

The particular table I am referring to has 63 million rows and gets inserted/updated constantly, all day, every day.
BMWGuy
New Member
New Member

--
02 Jul 2009 10:30 AM
We update stats weekly and re-index monthly.



BMWGuy
New Member
New Member

--
02 Jul 2009 12:44 PM
Which fragmentation metric is most important to look at:

-Average Page Density
-Scan Density
-Logical Fragmentation
-Extent Fragmentation



BMWGuy
New Member
New Member

--
09 Jul 2009 05:57 AM
avg_page_space_used_in_percent is NULL for every index. Why would that be?



You are not authorized to post a reply.

Acceptable Use Policy