{"id":612,"date":"2021-11-15T16:32:51","date_gmt":"2021-11-15T16:32:51","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=612"},"modified":"2021-11-15T16:32:53","modified_gmt":"2021-11-15T16:32:53","slug":"fillfactor-the-untold-story","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2021\/11\/15\/fillfactor-the-untold-story\/","title":{"rendered":"FillFactor, lo que Nadie te ha Contado"},"content":{"rendered":"\nThere are some misconceptions around FillFactor, let’s explore what the setting does or doesn’t do \n\n\n\n

I love SQL Server internals, I do and I just said it.<\/p>\n\n\n\n

Why? because thanks to all the tools, documentation and community members that share their knowledge, folks like me can understand how a super complex piece of software like a relational database engine works (or at least a small part of it).<\/p>\n\n\n\n

And I’m definitely not a genius, unfortunately \ud83d\ude42 <\/p>\n\n\n\n

 <\/p>\n\n\n\n

Background<\/h3>\n\n\n\n

Most of us DBAs are familiar with the concept of index fragmentation and understand that in order to fix it, we need to have appropriate routines to remove it.<\/p>\n\n\n\n

Also is wide known that in order to minimize it (fragmentation) we can tweak something called FILLFACTOR.<\/p>\n\n\n\n

From Books <\/a>O<\/a>nline<\/a>:<\/p>\n\n\n\n

When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty<\/strong><\/p><\/blockquote>\n\n\n\n

 <\/p>\n\n\n\n

The Expectation<\/h3>\n\n\n\n

By reading the documentation and taking the example of FillFactor 80, you might get the impression that in the index (clustered or not) we will always keep this 20% free space to prevent fragmentation, including the new pages allocated after you create or rebuild the index<\/strong><\/em>.<\/p>\n\n\n\n

That sounds really cool, if each page keeps 20 percent free, future rows can be allocated or current rows with variable length columns can be expanded without incurring in page splits.<\/p>\n\n\n\n

 <\/p>\n\n\n\n

The reality<\/h3>\n\n\n\n

Let’s see it in an example, I will create and load one table with FILLFACTOR 100 and another with FILLFACTOR 10 (to see the extreme), then we will see how the content of a page from each table are different.<\/p>\n\n\n\n

\nUSE fill_factor\n\nDROP TABLE IF EXISTS dbo.Seq_100 \nCREATE TABLE dbo.Seq_100 (\n\tId INT IDENTITY(1,10) NOT NULL,\n\tCol1 CHAR(50) DEFAULT REPLICATE('A', 50)\n)\nALTER TABLE dbo.Seq_100 ADD CONSTRAINT Pk_Seq_100 PRIMARY KEY (Id)\nWITH( FILLFACTOR = 100)\nGO\n\nINSERT INTO dbo.Seq_100\nDEFAULT VALUES \nGO 1000\n\nDROP TABLE IF EXISTS dbo.Seq_10 \nCREATE TABLE dbo.Seq_10 (\n\tId INT IDENTITY(1,10) NOT NULL,\n\tCol1 CHAR(50) DEFAULT REPLICATE('A', 50)\n) \nALTER TABLE dbo.Seq_10 ADD CONSTRAINT Pk_Seq_10 PRIMARY KEY (Id)\nWITH( FILLFACTOR = 10)\nGO\n\nINSERT INTO dbo.Seq_10\nDEFAULT VALUES \nGO 1000\n<\/pre>\n\n\n\n
Tables are created and loaded, so let’s have a look to both table’s metadata: <\/p>\n\n\n\n
SELECT OBJECT_NAME(object_id) AS [object_name], * \nFROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Seq_100'),1, NULL, 'DETAILED') \nSELECT OBJECT_NAME(object_id) AS [object_name], * \nFROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Seq_10'),1, NULL, 'DETAILED') \n<\/pre>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Don’t know you, but I’d say those tables<\/s> clustered indexes look very much the identical, same number of rows, fragmentation, percentage of space used? hm what is going on?<\/p>\n\n\n\n
Let’s get a random page from each and compare the headers<\/p>\n\n\n\n
\nDBCC IND('fill_factor', 'dbo.Seq_100', 1)\nDBCC TRACEON (3604)\n-- Your page number will be different\nDBCC PAGE (fill_factor, 1, 328, 3);\n<\/pre>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\nDBCC IND('fill_factor', 'dbo.Seq_10', 1)\nDBCC TRACEON (3604)\n-- Your page number will be different\nDBCC PAGE (fill_factor, 1, 384, 3);\n<\/pre>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
m_freeCnt stores the number for bytes free in the page, in both cases 32 bytes.<\/p>\n\n\n\n
So, both tables’ pages are filled until there wasn’t enough space to add another row, regardless of the FILLFACTOR, yes, not joking.<\/p>\n\n\n\n
What is the point of configuring it then? Let’s rebuild the one with FILLFACTOR 10 and check again physical stats.<\/p>\n\n\n\n
\nALTER INDEX Pk_Seq_10 ON dbo.Seq_10 REBUILD\n\nSELECT OBJECT_NAME(object_id) AS [object_name], * \nFROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Seq_10'),1, NULL, 'DETAILED') \n<\/pre>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Now after rebuilding, we can see that the percentage used per page matches the FILLFACTOR, about 10%.<\/p>\n\n\n\n
If we add more rows, subsequent new pages created will be filled to 100% again, which makes me really sad.<\/p>\n\n\n\n
And the key point was When an index is created or rebuilt<\/em><\/strong>, which obviously does not affect to new allocated pages and that includes all the data inserted after creating a new table for instance.<\/p>\n\n\n\n
 <\/p>\n\n\n\n
Can we do better, Microsoft?<\/h3>\n\n\n\n
I believe yes, we can do better, let me explain.<\/p>\n\n\n\n
SQL Server index pages are a double linked chain, so each page points to the previous and to the next, this can be seen using DBCC IND or in the page header itself.<\/p>\n\n\n\n
Would it be possible that the SQL Server engine honor the FILLFACTOR if the page is the last page (does not link to a next page)? I think yes, in the same way SQL Server needs to know how much free space is in a page before trying to put some data there.<\/p>\n\n\n\n
Once the page is no longer the last of the table\/index, SQL Server should ignore FILLFACTOR and write until 100% full or even split if that was the case.<\/p>\n\n\n\n
Proceeding as explained, all pages would initially have space to prevent the terrible fragmentation we have been fighting every night since decades ago.<\/p>\n\n\n\n
This is just an idea.<\/p>\n\n\n\n
 <\/p>\n\n\n\n
Conclusion<\/h3>\n\n\n\n
I whish FILLFACTOR had some sort of mechanism that will start writing new pages once we reach the magical number instead of applying only to the existing data <\/em><\/strong>when we create or rebuild an index, clustered or not.<\/p>\n\n\n\n
Unfortunately, it is not the case and new pages will be always written until there is no more space, leading to future fragmentation, which was what we were trying to avoid by changing FILLFACTOR.<\/p>\n\n\n\n
This problem is somehow exacerbated in clustered indexes or in Standard Edition, where online rebuilds are not a thing or if you cannot allow a maintenance window every so often.<\/p>\n\n\n\n
Hope you enjoyed reading and please feel free to use the comments below.<\/p>\n\n\n\n
 <\/p>\n\n\n\n
Thanks! <\/p>\n","protected":false},"excerpt":{"rendered":"
There are some misconceptions around FillFactor, let’s explore what the setting does or doesn’t do  I love SQL Server internals, I do and I just said it. Why? because thanks to all…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[35,55,12,25],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/612"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=612"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/612\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=612"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=612"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=612"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}