Extent Management in SQL Server

Today I’m talking about Extent Management in SQL Server, because this is a very important topic, especially when you deal with TempDb in SQL Server. On a very high level an extent is just a group of 8 pages of 8kb. An extent is therefore always a chunk of 64kb. SQL Server internally implements 2 kinds of extents:

Mixed Extents
Uniform Extents

Let’s have a more detailed look at both kinds.

Mixed & Uniform Extents

In a mixed extent the 8 pages can belong to different database objects like tables and indexes. This also means that a mixed extent can point to 8 different database objects. On the other hand, in a uniform extent all 8 pages belong to the same database object. The question is now, why does SQL Server make that differentiation? It’s mainly a historical reason. Let’s try to describe it.

In the last millennium storage was very, very expensive. The goal in that time was to use storage as effectively as possible. For that reason, the first 8 pages of new tables and indexes are always allocated in mixed extents. This means that your table/index grows in 8kb chunks at the beginning. As a result, small tables stay very small. You are just using your storage as effectively as possible.

As soon as your database object needs to allocate a 9th page, SQL Server allocates a whole uniform extent to that database object. The size of the object grows from 8 to 72kb, for the 17th page it grows from 72 to 136kb and so on and on. Nowadays you just shake your head about that fact, but in the last millennium this was a very important design choice. The following picture shows (in a very simplified way) what an extent looks like:

Extents in SQL Server

Extent Management

The question is now how SQL Server manages all these extents? Imagine you have a database with a size of 1 TB – this will give you a huge number of extents. SQL Server uses here 2 special pages, which again have a size of 8kb:

Global Allocation Map Pages (GAM)
Shared Global Allocation Map Pages (SGAM)

Uniform extents are always managed by GAM pages. SQL Server uses on a GAM page 8000 bytes, which gives you 64000 bits. (8000 x 8). Every bit in that huge bitmap mask represents one uniform extent. If the bit is set, the uniform extent is free, if the bit is not set, the uniform extent is used. This also means that you can only manage with one GAM page an interval of 4 GB of data (64000 x 64 / 1024 / 1024). Therefore, GAM pages are coming regularly in your data file – every 4 GB. And the same is true for SGAM pages. An SGAM page also manages a data interval of 4 GB, because you have 64000 bits available.

When you are inserting a new row into a table, SQL Server just finds through the various SGAM pages a mixed extent that has at least one free page available. If your table/index is larger than 64kb, then SQL Server just finds a free uniform extent through the various GAM pages. Easy, isn’t it?

Summary

Today I have talked about Extents and Extent

Management in SQL Server. By now you should already have a very good, solid

understanding how SQL Server is internally structured. If you want to learn

more about extents, I highly recommend watching the following SQL Server

Quickies about it:

Thanks for your time,

-Klaus