// Week 3: Extent Management in SQL Server

Wow, it’s already the 3rd week in the SQLpassion Performance Tuning Training Plan! In the mean time you already have a very good understanding about how SQL Server works internally. Today I’m talking about Extent Management in SQL Server, because this is a very important topic, when we cover TempDb in week 23. 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:

1. Mixed Extents
2. Uniform Extents

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

Mixed and 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 are belonging to the same database object. The question is now, why SQL Server is making that differentiation? It’s mainly a historical reason. Let’s try to describe it.

In the last millenium storage was very, very expensive. The goal in that time was to use storage as effective 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 are staying very small. You are just using your storage as effective 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 are just shaking your head about that fact, but in the last millenium this was a very important design choice. The following picture shows very simplified how an extent looks like:

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 amount of extents. SQL Server uses here 2 special pages, which are again have a size of 8kb:

1. Global Allocation Map Pages (GAM)
2. 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 regularily 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 inserting a new row into a table, SQL Server just finds through the various SGAM pages an 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?

As you will see in week 23, when we talk about TempDb, this will introduce serious performance problems, when your workload tries to create a huge amount of temp tables concurrently in TempDb. Here we will also discuss how you can overcome this problem with some configuration changes in your TempDb database.

Summary

In this installment of the SQL Server Performance Tuning series we have talked about Extents and Extent Management in SQL Server. By now you have already a very good, solid understanding how SQL Server is internally structured.

As you will see in the later installments, this knowledge is just a prerequisite for performance tuning & troubleshooting. I hope you had a fun with this newsletter today, and I’m back to you next week, when you learning some limitations about data pages, and how we can fight against them. Stay tuned!