Share via

Space Allocation: ‘Extents’ in SQL Server

  • Article

A database needs space for storing Tables and Indexes and in the SQL server the space is managed in Units called ‘Extents’.

  1. Now what is an ‘Extent’?

An Extent is a 64 Kb Contiguous Space or in better terms it is made up of Eight Logically Contiguous Pages.

  1. Types of Extent:

Uniform Extent: If an entire Extent is owned by a Single Object. i.e. All the eight pages in an extent can be used by an object which is the owner of that Extent.

Mixed Extents: When an Extent is shared by a multiple Objects it is called a Mixed Extent. An Extent can be shared maximum by 8 objects.

  1. How SQL Server Allocates:

Whenever a Table or Index is initialized SQL server allocates pages to that new table or index from the mixed extents. And if the object grows more than 8 pages, the future allocations are made in the Uniform Extents.

So whenever a Table or Index is smaller than 8 pages the SQL server needs to find a mixed event with Space Available and if it more than 8 pages it needs to find a Uniform Extent.

To find these extents (which are allocated which are not) SQL server uses two types of Pages

  1. GAM Pages: GAM abbreviates as Global Allocation Map. These GAM pages keep track of which extents have been used (Both uniform and mixed). If the value is 1 it indicates the extent is free and if its 0 it indicates the extent is occupied.
  2. SGAM Pages: SGAM abbreviates as Shared Global Allocation Map. These SGAM pages keep track of which extent are Mixed and have at least one free page for allocation. A value of 0 indicates that the extent is not a mixed extent or it has no free pages left. And a value of 1 indicates that it is a mixed extent and it has free pages.

Now in a page after the header and other overheads are occupied, 64,000 bits or 8000 bytes are available. So each GAM or SGAM page can keep track of 64,000 extents. So calculating each GAM or SGAM page can map 64000*64 KB = 4000 MB. So for every 4000 MB there is a GAM and a SGAM page and they cover all the extents in this space as below.

BitPattern in GAM and SGAM Pages

Type of Extent

GAM bit

SGAM bit

Completely free

1

0

Uniform Extent or Filled Mixed Extent

0

0

Mixed Extent with free pages

0

1

                               

If SQL server needs a completely free extent it will search for extents which have a GAM bit of 1. If it needs to find a Mixed Extent with free space it will search for extents which have SGAM as 1.

 

And if there are no Mixed Extents with free space it finds a whole new extent and makes it’s a Mixed Extent and if there are no free extents then the file is full.

In a file the 3rd page is a GAM and the fourth one is a SGAM and every another GAM appears after around estimated 4000 MB that is around 512000 Pages ( Precisely to say after 511230 pages). The same is with the SGAM.