{"id":2265,"date":"2014-03-19T11:00:04","date_gmt":"2014-03-19T15:00:04","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2265"},"modified":"2014-11-13T13:41:02","modified_gmt":"2014-11-13T18:41:02","slug":"extent","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2265\/extent\/","title":{"rendered":"The Extent (A Storage Wednesday Post)"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nA few weeks ago I said that SQL Server stores everything in pages<\/a>, each 8192 bytes in size. Today I would like to revisit that statement and go into a little more detail of how SQL Server organizes all those pages.\n<\/p>\n

Extents<\/h3>\n

\nA page is 8KB in size, which means 1MB of files space fits 128 pages. However, we do not really measure our file sizes in MB anymore. Database several hundred GB in size are not uncommon at all anymore. 100GB of file space contains over 13 million pages. To put that into perspective, one 8KB page contains just 65535 single bits.\n<\/p>\n

\nThe reason I am comparing counts of pages to counts of bits in a page is, that SQL Server needs to be able to quickly find an unused page when a table needs to grow. That means SQL Server needs a catalog somewhere indicating for each page if it is still available or not. To keep a catalog of free pages, you need at least a single bit per page. To make this a little more manageable, SQL Server groups pages into sections of eight. Each eight-page section is called an \"Extent\". An Extent is therefore 64KB in size and this is the smallest unit SQL Server will reserve when creating or growing a table.\n<\/p>\n

Mixed Extents<\/h3>\n

\nOne extent or 64KB is the smallest block that SQL Server will reserve in the file when growing a table. However, a table can still be smaller than 64KB. At the time this mechanism was introduced, 64KB was still a rather large chunk of disk space. To not waste space on disk when creating many small tables SQL Server introduced the concept of mixed extents (also called shared extents). A mixed extent contains pages that do not necessarily belong to the same table.\n<\/p>\n

\n\"A<\/a>\n<\/p>\n

\nWhen a table is newly created and the first row is inserted, a single page is reserved for that table. That page is taken from any mixed extent that still has an unused page within. If there is no mixed extend with a free page available, a new extend is reserved on disk and marked as a mixed extent. Then one page from that new extend is allocated to the table. The next seven times the table has to grow, another page of any available mixed extent is allocated to the table.\n<\/p>\n

Uniform Extents<\/h3>\n

\nOnce the table grows beyond eight pages, every new allocation of space for that table will always be an entire extend. Extents that are allocated this way are called uniform extents.\n<\/p>\n

\n\"A<\/a>\n<\/p>\n

\nKeep in mind however that an extend that was reserved as a mixed extent, stays a mixed extent event if it fills up with pages only from a single table.\n<\/p>\n

Trace Flag 1118<\/h3>\n

\nThere is some overhead involved with this flexibility and with today's storage prices you might want to consider disabling mixed extents completely in your databases. To achieve that you can enable trace flag 1118 by adding the -T1118<\/span> startup parameter to the SQL Server instance. There are several sources on the web that recommend to always turn TF 1118 on, e.g. Paul Randal<\/a>. One of the main reasons is that it can help alleviate tempdb<\/span> contention. That is also the reason why most posts about TF 1118 do not mention if this trace flag has any effect on other databases. However, you can be assured that it affects any new table, no matter, which database it is created in.\n<\/p>\n

Summary<\/h3>\n

\nSQL Server stores all information split into 8KB blocks called pages. However, the smallest unit that SQL Server will reserve on disk when creating or growing a table is called an extent, which always consists of eight 8KB pages. SQL Server provides a mechanism to allow tables smaller the 64KB. This utilizes so-called mixed extents that can hold pages belonging to separate tables.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

SQL Server organizes database pages in groups of 8 called extents. An extent is the smallest unit that SQL Server will reserve on disk. However, smaller tables are still possible through mixed extents. Read on to get all the details.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5,27,14,105],"tags":[138,116,137,107,15,108,109],"class_list":["post-2265","post","type-post","status-publish","format-standard","hentry","category-general","category-series","category-sql-server-internals","category-storage-wednesday","tag-allocation","tag-allocation-unit","tag-extent","tag-page","tag-sql-server","tag-storage","tag-storage-engine"],"acf":[],"yoast_head":"\nThe Extent (A Storage Wednesday Post) - sqlity.net<\/title>\n<meta name=\"description\" content=\"SQL Server organizes pages in groups of 8 called extents. An extent is the smallest unit that SQL Server will reserve. However, smaller tables are still possible through mixed extents.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2265\/extent\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Extent (A Storage Wednesday Post) - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"SQL Server organizes pages in groups of 8 called extents. An extent is the smallest unit that SQL Server will reserve. However, smaller tables are still possible through mixed extents.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2265\/extent\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-03-19T15:00:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:41:02+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/a_mixed_extent.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"The Extent (A Storage Wednesday Post)\",\"datePublished\":\"2014-03-19T15:00:04+00:00\",\"dateModified\":\"2014-11-13T18:41:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/\"},\"wordCount\":648,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/a_mixed_extent.jpg\",\"keywords\":[\"allocation\",\"Allocation Unit\",\"Extent\",\"page\",\"SQL Server\",\"storage\",\"storage engine\"],\"articleSection\":[\"General\",\"Series\",\"SQL Server Internals\",\"Storage Wednesday\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/\",\"name\":\"The Extent (A Storage Wednesday Post) - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/a_mixed_extent.jpg\",\"datePublished\":\"2014-03-19T15:00:04+00:00\",\"dateModified\":\"2014-11-13T18:41:02+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"SQL Server organizes pages in groups of 8 called extents. An extent is the smallest unit that SQL Server will reserve. However, smaller tables are still possible through mixed extents.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/a_mixed_extent.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/a_mixed_extent.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2265\\\/extent\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Extent (A Storage Wednesday Post)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Extent (A Storage Wednesday Post) - sqlity.net","description":"SQL Server organizes pages in groups of 8 called extents. An extent is the smallest unit that SQL Server will reserve. However, smaller tables are still possible through mixed extents.","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:\/\/sqlity.net\/en\/2265\/extent\/","og_locale":"en_US","og_type":"article","og_title":"The Extent (A Storage Wednesday Post) - sqlity.net","og_description":"SQL Server organizes pages in groups of 8 called extents. An extent is the smallest unit that SQL Server will reserve. However, smaller tables are still possible through mixed extents.","og_url":"https:\/\/sqlity.net\/en\/2265\/extent\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-03-19T15:00:04+00:00","article_modified_time":"2014-11-13T18:41:02+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/a_mixed_extent.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2265\/extent\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2265\/extent\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"The Extent (A Storage Wednesday Post)","datePublished":"2014-03-19T15:00:04+00:00","dateModified":"2014-11-13T18:41:02+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2265\/extent\/"},"wordCount":648,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2265\/extent\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/a_mixed_extent.jpg","keywords":["allocation","Allocation Unit","Extent","page","SQL Server","storage","storage engine"],"articleSection":["General","Series","SQL Server Internals","Storage Wednesday"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2265\/extent\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2265\/extent\/","url":"https:\/\/sqlity.net\/en\/2265\/extent\/","name":"The Extent (A Storage Wednesday Post) - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2265\/extent\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2265\/extent\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/a_mixed_extent.jpg","datePublished":"2014-03-19T15:00:04+00:00","dateModified":"2014-11-13T18:41:02+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"SQL Server organizes pages in groups of 8 called extents. An extent is the smallest unit that SQL Server will reserve. However, smaller tables are still possible through mixed extents.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2265\/extent\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2265\/extent\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2265\/extent\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/a_mixed_extent.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/a_mixed_extent.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2265\/extent\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"The Extent (A Storage Wednesday Post)"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/s2wXuw-extent","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2265","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2265"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2265\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2265"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2265"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2265"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}