{"id":702,"date":"2010-04-25T09:51:00","date_gmt":"2010-04-25T09:51:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(2530)-fill-factor.aspx"},"modified":"2017-05-08T11:52:31","modified_gmt":"2017-05-08T18:52:31","slug":"a-sql-server-dba-myth-a-day-2530-fill-factor","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/","title":{"rendered":"A SQL Server DBA myth a day: (25\/30) fill factor"},"content":{"rendered":"<p style=\"text-align: justify;\">(Look in the <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/category\/misconceptions\/\">Misconceptions<\/a> blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our <a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">Insider list<\/a>, plus my online <a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-myths\" target=\"_blank\" rel=\"noopener noreferrer\">Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n<p style=\"text-align: justify;\">I&#8217;m all mythed-out from yesterday, so today&#8217;s is a quick post addressing some myths around fill factor &#8211; which I made sure to stomp on back in SQL Server 2005 in Books Online.<\/p>\n<p style=\"text-align: justify;\">Tomorrow I have a very cool one coming up&#8230;<\/p>\n<p><strong>Myth #25:<\/strong> <em>various myths around fill factor<\/em>.<\/p>\n<p><strong><u><em>All are FALSE<\/em><\/u><\/strong><\/p>\n<p><strong>25a)<\/strong> <em>fill factor is adhered to at all times<\/em><\/p>\n<p>No. From <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms177459.aspx\">Books Online<\/a>:<\/p>\n<blockquote>\n<p style=\"text-align: justify;\"><em>Important:<br \/>\nThe 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.<\/em><\/p>\n<\/blockquote>\n<p><strong>25b)<\/strong> <em>fill factor of 0 is different from fill factor of 100<\/em><\/p>\n<p>No: From <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms177459.aspx\">Books Online<\/a>:<\/p>\n<blockquote><p><em>Note:<br \/>\nFill-factor values 0 and 100 are the same in all respects.<\/em><\/p><\/blockquote>\n<p><strong>25c)<\/strong> <em>fill factor of 0 leaves some space in the upper levels of the index<\/em><\/p>\n<p style=\"text-align: justify;\">No. This one isn&#8217;t in Books Online and I don&#8217;t know where this myth came from, but it&#8217;s completely untrue. You can easily convince yourself of this using a script like the one below:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCREATE DATABASE foo;\r\nGO\r\nUSE foo;\r\nGO\r\nCREATE TABLE t1 (c1 INT IDENTITY, c2 CHAR (1000) DEFAULT 'a');\r\nCREATE CLUSTERED INDEX t1c1 ON t1 (c1);\r\nGO\r\nSET NOCOUNT ON;\r\nGO\r\nINSERT INTO t1 DEFAULT VALUES;\r\nGO 10000\r\n<\/pre>\n<p>Now\u00a0check the fill factor is 0 and perform an index rebuild.<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSELECT &#x5B;fill_factor] FROM sys.indexes\r\nWHERE NAME = 't1c1' AND &#x5B;object_id] = OBJECT_ID ('t1');\r\nGO\r\nALTER INDEX t1c1 ON t1 REBUILD WITH (FILLFACTOR = 100);\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">Then figure out the index pages above the leaf level and look at the <em>m_freeCnt<\/em> value in the page header, the amount of free space on the page:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nEXEC sp_allocationMetadata 't1';\r\nGO\r\nDBCC TRACEON (3604);\r\n\r\n-- the root page, from the SP output\r\nDBCC PAGE (foo, 1, 164, 3);\r\nGO\r\n\r\n-- the page ID in the DBCC PAGE output above\r\nDBCC PAGE (foo, 1, 162, 1);\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">I see a value of 10 bytes &#8211; clearly no space was left on the page. It&#8217;s a myth. Btw &#8211; you can get the <em>sp_allocationMetadata<\/em> script from <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/\">this blog post<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Look in the Misconceptions blog category for the rest of the month&#8217;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&#8217;m all mythed-out from yesterday, so today&#8217;s is a quick [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,48,61,62],"tags":[],"class_list":["post-702","post","type-post","status-publish","format-standard","hentry","category-indexes-from-every-angle","category-inside-the-storage-engine","category-misconceptions","category-on-disk-structures"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>A SQL Server DBA myth a day: (25\/30) fill factor - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQL Server DBA myth a day: (25\/30) fill factor - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Look in the Misconceptions blog category for the rest of the month&#8217;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&#8217;m all mythed-out from yesterday, so today&#8217;s is a quick [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-25T09:51:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-05-08T18:52:31+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/\",\"name\":\"A SQL Server DBA myth a day: (25\/30) fill factor - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-25T09:51:00+00:00\",\"dateModified\":\"2017-05-08T18:52:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQL Server DBA myth a day: (25\/30) fill factor\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A SQL Server DBA myth a day: (25\/30) fill factor - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/","og_locale":"en_US","og_type":"article","og_title":"A SQL Server DBA myth a day: (25\/30) fill factor - Paul S. Randal","og_description":"(Look in the Misconceptions blog category for the rest of the month&#8217;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&#8217;m all mythed-out from yesterday, so today&#8217;s is a quick [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-25T09:51:00+00:00","article_modified_time":"2017-05-08T18:52:31+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/","name":"A SQL Server DBA myth a day: (25\/30) fill factor - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-25T09:51:00+00:00","dateModified":"2017-05-08T18:52:31+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2530-fill-factor\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"A SQL Server DBA myth a day: (25\/30) fill factor"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/702","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=702"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/702\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=702"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=702"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=702"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}