A SQL Server DBA myth a day: (25/30) fill factor

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

I’m all mythed-out from yesterday, so today’s is a quick post addressing some myths around fill factor – which I made sure to stomp on back in SQL Server 2005 in Books Online.

Tomorrow I have a very cool one coming up…

Myth #25: various myths around fill factor.

All are FALSE

25a) fill factor is adhered to at all times

No. From Books Online:

Important:
The fill-factor setting applies only when the index is created, or rebuilt. The SQL Server Database Engine does not dynamically keep the specified percentage of empty space in the pages. Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered.

25b) fill factor of 0 is different from fill factor of 100

No: From Books Online:

Note:
Fill-factor values 0 and 100 are the same in all respects.

25c) fill factor of 0 leaves some space in the upper levels of the index

No. This one isn’t in Books Online and I don’t know where this myth came from, but it’s completely untrue. You can easily convince yourself of this using a script like the one below:

CREATE DATABASE foo;
GO
USE foo;
GO
CREATE TABLE t1 (c1 INT IDENTITY, c2 CHAR (1000) DEFAULT 'a');
CREATE CLUSTERED INDEX t1c1 ON t1 (c1);
GO
SET NOCOUNT ON;
GO
INSERT INTO t1 DEFAULT VALUES;
GO 10000

Now check the fill factor is 0 and perform an index rebuild.

SELECT [fill_factor] FROM sys.indexes
WHERE NAME = 't1c1' AND [object_id] = OBJECT_ID ('t1');
GO
ALTER INDEX t1c1 ON t1 REBUILD WITH (FILLFACTOR = 100);
GO

Then figure out the index pages above the leaf level and look at the m_freeCnt value in the page header, the amount of free space on the page:

EXEC sp_allocationMetadata 't1';
GO
DBCC TRACEON (3604);

-- the root page, from the SP output
DBCC PAGE (foo, 1, 164, 3);
GO

-- the page ID in the DBCC PAGE output above
DBCC PAGE (foo, 1, 162, 1);
GO

I see a value of 10 bytes – clearly no space was left on the page. It’s a myth. Btw – you can get the sp_allocationMetadata script from this blog post.

10 thoughts on “A SQL Server DBA myth a day: (25/30) fill factor

  1. I can tell you the origins of myth 25c, the section in BOL that talks about PAD_INDEX (http://msdn.microsoft.com/en-us/library/ms188783.aspx). It says that if PAD_INDEX is off (which is the default) "The intermediate-level pages are filled to near capacity ..". Since I know that you know what you are talking about, am I misunderstanding BOL or is their explanation wrong?

  2. I think that myth in 28c might come from a different source. If memory serves, there was a comment in either an earlier version of BOL, or possibly one of the MOC courses, that said that either 0 or 100 (can’t remember which) left a certain number of entries in the non-leaf nodes. I think it was a MOC course, because I remember it coming up in my classes. I told my students that they both meant "full" and that the only thing I could glean from them was that I could look in the catalog and use 0 versus 100 to determine whether the fillfactor was the default value or an explicitly-requested one.

    1. Chris is correct. It was in a MOC course, but I can’t remember which one. I think it was even on the exam. Paul, I love when you add scripts that prove things because so many times there have been problems in BOL from time to time that have started these wonderful misconceptions.

      1. BOL is actually correct.

        In SQL2000 0 means 0 and 100 means 100.

        It’s SQL2005 where they screwed the things up.

        1. I don’t follow you – in SQL 2000, 0 and 100 both mean 100, as they do in every release since then. I put clarification in Books Online to clear up the misconception that they mean different things, because they don’t.

  3. Nice Post..!!! This is very important blog,thanks for sharing with us. I believe that legend in 28c may originate from an alternate source. In the event that memory serves, there was a remark in either a prior rendition of BOL, or potentially one of the MOC courses, that said that either 0 or 100 (can’t recollect which) left a specific number of passages in the non-leaf hubs. I think it was a MOC course, since I recall it coming up in my classes. I told my understudies that they both signified “full” and that the main thing I could gather from them was that I could look in the list and utilize 0 versus 100 to decide if the fillfactor was the default esteem or an unequivocally asked for one.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.