{"id":3507,"date":"2018-10-02T06:15:46","date_gmt":"2018-10-02T06:15:46","guid":{"rendered":"https:\/\/dbtut.com\/?p=3507"},"modified":"2018-11-24T21:18:48","modified_gmt":"2018-11-24T21:18:48","slug":"sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/","title":{"rendered":"SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM)"},"content":{"rendered":"

 <\/p>\n

Page is the most basic unit of SQL Server on storage. Each page consists of 8 KB.<\/p>\n

The mdf or ndf files that allocate disks from the operating system are logically divided into pages.<\/p>\n

IO on disk is performed at page level. SQL Server uses Extents to manage the Page.<\/p>\n

Each Extent consists of 8 pages and has a size of 64 KB. There are two types of Extent.<\/p>\n

 <\/p>\n

1- Uniform Extent<\/h3>\n

Uniform Extent is the extent type that has only one object.<\/p>\n

 <\/p>\n

2- Mixed Extent<\/h3>\n

Mixed Extents is the extent type that have multiple objects.<\/p>\n

 <\/p>\n

SQL Server does not directly allocate a Uniform Extent to an object to use the domain more efficiently.<\/p>\n

First, it creates the Extent as a Mixed Extent.<\/p>\n

If the same object grows enough to fill 8 pages, this generated Mixed Extent is then converted to Uniform Extent.<\/p>\n

You should read the article titled “Can We Use Our Disks at Maximum Performance?”<\/p>\n

 <\/p>\n

The page types in SQL Server are as follows:<\/h3>\n
\n\n\n\n\n\n\n\n\n\n\n
Data Pages<\/td>\nAll data types except text, ntext, image, nvarchar (max), varchar (max), varbinary (max) and xml data type are kept on data pages.<\/td>\n<\/tr>\n
Index Pages<\/td>\nIndexes are kept on Index pages.<\/td>\n<\/tr>\n
Text\/Image Pages<\/td>\nText, ntext, image, nvarchar (max), varchar (max), varbinary (max) and xml data types are kept.<\/p>\n

If data size exceeds 8 KB for columns that use varchar, nvarchar, and varbinary data types, this data is kept on Text \/ Image pages.<\/td>\n<\/tr>\n

Global Allocation Map Pages,<\/p>\n

Shared Global Allocation Map Pages<\/td>\n

In the “Gam page” s, which extens are allocated and which are not allocated information is kept.<\/p>\n

In the “SGam page”s, which extents are used as Mixed Extent information is kept.<\/td>\n<\/tr>\n

Page Free Space Pages<\/td>\n“PFS page” have information on how much free space on which page is kept.<\/td>\n<\/tr>\n
Index Allocation Map Pages<\/td>\nWhich page belongs to which object is kept.<\/td>\n<\/tr>\n
Bulk Changed Map<\/td>\nAfter the last log backup, the information of the Extents that are changed by the bulk operations is kept.<\/td>\n<\/tr>\n
Differential Changed Map<\/td>\nAfter the last backup, the information of the changed Extents is kept.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n<\/div>\n
<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

  Page is the most basic unit of SQL Server on storage. Each page consists of 8 KB. The mdf or ndf files that allocate disks from the operating system are logically divided into pages. IO on disk is performed at page level. SQL Server uses Extents to manage the Page. Each Extent consists of …<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3507","post","type-post","status-publish","format-standard","","category-mssql"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":808,"today_views":0},"yoast_head":"\nSQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM) - Database Tutorials<\/title>\n<meta name=\"description\" content=\"SQL Server Storage Concepts (Page,Extents,GAM,SGAM,PFS,IAM,BCM,DCM)\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM) - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"SQL Server Storage Concepts (Page,Extents,GAM,SGAM,PFS,IAM,BCM,DCM)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-02T06:15:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-11-24T21:18:48+00:00\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\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\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM)\",\"datePublished\":\"2018-10-02T06:15:46+00:00\",\"dateModified\":\"2018-11-24T21:18:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/\"},\"wordCount\":363,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/\",\"name\":\"SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM) - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2018-10-02T06:15:46+00:00\",\"dateModified\":\"2018-11-24T21:18:48+00:00\",\"description\":\"SQL Server Storage Concepts (Page,Extents,GAM,SGAM,PFS,IAM,BCM,DCM)\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM) - Database Tutorials","description":"SQL Server Storage Concepts (Page,Extents,GAM,SGAM,PFS,IAM,BCM,DCM)","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:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM) - Database Tutorials","og_description":"SQL Server Storage Concepts (Page,Extents,GAM,SGAM,PFS,IAM,BCM,DCM)","og_url":"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/","og_site_name":"Database Tutorials","article_published_time":"2018-10-02T06:15:46+00:00","article_modified_time":"2018-11-24T21:18:48+00:00","author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM)","datePublished":"2018-10-02T06:15:46+00:00","dateModified":"2018-11-24T21:18:48+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/"},"wordCount":363,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/","url":"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/","name":"SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM) - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2018-10-02T06:15:46+00:00","dateModified":"2018-11-24T21:18:48+00:00","description":"SQL Server Storage Concepts (Page,Extents,GAM,SGAM,PFS,IAM,BCM,DCM)","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/10\/02\/sql-server-storage-concepts-pageextentsgamsgampfsiambcmdcm\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM)"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/3507","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=3507"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/3507\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=3507"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=3507"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=3507"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}