{"id":4870,"date":"2018-01-24T11:11:44","date_gmt":"2018-01-24T19:11:44","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4870"},"modified":"2018-09-28T01:28:57","modified_gmt":"2018-09-28T08:28:57","slug":"sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/","title":{"rendered":"SQLskills SQL101: Why does my heap have a bunch of empty pages?"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0<strong>SQLskills SQL101<\/strong>\u00a0blog posts, check out\u00a0<a href=\"https:\/\/www.sqlskills.com\/help\/SQL101\" target=\"_blank\" rel=\"noopener noreferrer\">SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n<p style=\"text-align: justify;\">Here&#8217;s a question I&#8217;m often asked (paraphrasing):<\/p>\n<p style=\"padding-left: 30px; text-align: justify;\"><em>I\u2019ve got a large heap table where the space isn\u2019t being given up when I delete a large number of records, but then when I shrink the database the heap is reduced in size. Can you explain?<\/em><\/p>\n<p style=\"text-align: justify;\">This behavior is how SQL Server works, but it\u2019s pretty non-intuitive. When a page\u00a0in an index becomes empty, it\u2019s always deallocated, as an empty page isn\u2019t allowed in a SQL\u00a0Server index structure from SQL Server 2005 onward. However, the structure of a heap is\u00a0different and as a result, the behavior is too.<\/p>\n<p style=\"text-align: justify;\">Whenever a row is deleted in a heap, it\u2019s\u00a0usually the case\u00a0that the page containing the row does not become empty. However, if the heap page that the row is stored on becomes empty as a result of the delete, the page\u00a0cannot be deallocated from the table unless an exclusive (X)\u00a0<span style=\"text-decoration: underline;\">table<\/span> lock is held (to remove the page from the internal free-space \u201ctracking\u201d mechanism that is in place for heaps). It&#8217;s not common that a table level X lock is held\u00a0unless <a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/ms184286(v=sql.105).aspx\" target=\"_blank\" rel=\"noopener noreferrer\">lock escalation<\/a> has occurred (because you\u2019re\u00a0deleting enough rows to trigger escalation from individual row X locks to a single table X lock), or if you specifically use the <em>TABLOCK<\/em> hint on the\u00a0delete statement, for instance. But, because both of these circumstances are unlikely, the empty heap page usually\u00a0cannot be deallocated.<\/p>\n<p style=\"text-align: justify;\">There is a Knowledge Base article that describes this phenomenon: <a href=\"http:\/\/support.microsoft.com\/kb\/913399\" target=\"_blank\" rel=\"noopener noreferrer\">KB 913399<\/a>. However, the\u00a0KB article only references up to and including SQL Server 2005 but this behavior exists in all version\u00a0and is very easy to reproduce if you want to prove it to yourself. Also, if you&#8217;re using one of the flavors of snapshot isolation then even using <em>TABLOCK<\/em> won&#8217;t allow the pages to be deallocated.<\/p>\n<p style=\"text-align: justify;\">The script below will create a database, create a table with one row per heap page, show you how many pages and rows, and how full each page is. Next it&#8217;ll\u00a0delete all the rows, and show you the pages are still there, with no rows, and empty. Give it a try!<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCREATE DATABASE &#x5B;EmptyHeapTest];\r\nGO\r\nUSE &#x5B;EmptyHeapTest];\r\nGO\r\n\r\nCREATE TABLE &#x5B;test] (&#x5B;c1] INT IDENTITY, &#x5B;c2] CHAR (8000) DEFAULT 'a');\r\nGO\r\n\r\nSET NOCOUNT ON;\r\nGO\r\n-- Insert 1000 rows, which will allocate 1000 pages\r\nINSERT INTO &#x5B;test] DEFAULT VALUES;\r\nGO 1000\r\n\r\n-- How many pages and how full?\r\nSELECT &#x5B;page_count], &#x5B;record_count], &#x5B;avg_page_space_used_in_percent]\r\nFROM sys.dm_db_index_physical_stats (DB_ID (N'EmptyHeapTest'), OBJECT_ID (N'test'), 0, \tDEFAULT, 'DETAILED');\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\npage_count           record_count         avg_page_space_used_in_percent\r\n-------------------- -------------------- ------------------------------\r\n1000                 1000                 98.974549048678\r\n<\/pre>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n-- Delete all the rows\r\nDELETE FROM &#x5B;test];\r\nGO\r\n\r\n-- Did all the pages get deleted?\r\nSELECT &#x5B;page_count], &#x5B;record_count], &#x5B;avg_page_space_used_in_percent]\r\nFROM sys.dm_db_index_physical_stats (DB_ID (N'EmptyHeapTest'), OBJECT_ID (N'test'), 0, \tDEFAULT, 'DETAILED');\r\nGO <\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\npage_count           record_count         avg_page_space_used_in_percent\r\n-------------------- -------------------- ------------------------------\r\n1000                 0                    0\r\n<\/pre>\n<p style=\"text-align: justify;\">The empty pages will be reused by subsequent inserts (into the same table, of course), but if the space isn\u2019t going to be reused\u00a0following a large delete in a heap, you might consider using the <em>TABLOCK<\/em> hint to allow the\u00a0empty pages to be deallocated and the space made available for other objects in the database to\u00a0use.<\/p>\n<p style=\"text-align: justify;\">Another alternative is to just use a clustered index instead, or if a heap is\u00a0necessary, you could\u00a0rebuild the heap using <em>ALTER TABLE \u2026 REBUILD<\/em> (that was added in SQL Server 2008 to\u00a0support enabling compression on a heap), with the caveat that this will cause all the table\u2019s\u00a0nonclustered indexes to be rebuilt.<\/p>\n<p style=\"text-align: justify;\">On the extreme end (in my opinion), you could reclaim the empty heap space using a shrink\u00a0operation. Shrink won\u2019t free up space inside pages as it moves them (with the exception of\u00a0compacting LOB pages as it goes \u2013 somewhat unsuccessfully depending on which version and\u00a0build you\u2019re on \u2013 see <a href=\"https:\/\/support.microsoft.com\/en-us\/kb\/2967240\" target=\"_blank\" rel=\"noopener noreferrer\">KB 2967240<\/a>), but it will remove empty pages rather than moving them.\u00a0This will effectively shrink the heap after a large delete, but with the usual caveats about shrink\u00a0causing index fragmentation and generally being an expensive, slow operation to perform.<\/p>\n<p style=\"text-align: justify;\">So now you know why you may have a bunch of empty pages in your heaps!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Here&#8217;s a question I&#8217;m often asked (paraphrasing): [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,62,108],"tags":[],"class_list":["post-4870","post","type-post","status-publish","format-standard","hentry","category-inside-the-storage-engine","category-on-disk-structures","category-sql101"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLskills SQL101: Why does my heap have a bunch of empty pages? - 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\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Why does my heap have a bunch of empty pages? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Here&#8217;s a question I&#8217;m often asked (paraphrasing): [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-24T19:11:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-09-28T08:28:57+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=\"4 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\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/\",\"name\":\"SQLskills SQL101: Why does my heap have a bunch of empty pages? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2018-01-24T19:11:44+00:00\",\"dateModified\":\"2018-09-28T08:28:57+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Why does my heap have a bunch of empty pages?\"}]},{\"@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":"SQLskills SQL101: Why does my heap have a bunch of empty pages? - 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\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Why does my heap have a bunch of empty pages? - Paul S. Randal","og_description":"SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Here&#8217;s a question I&#8217;m often asked (paraphrasing): [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/","og_site_name":"Paul S. Randal","article_published_time":"2018-01-24T19:11:44+00:00","article_modified_time":"2018-09-28T08:28:57+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/","name":"SQLskills SQL101: Why does my heap have a bunch of empty pages? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2018-01-24T19:11:44+00:00","dateModified":"2018-09-28T08:28:57+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Why does my heap have a bunch of empty pages?"}]},{"@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\/4870","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=4870"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4870\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4870"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4870"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4870"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}