I today’s blog posting I want to talk about Extent Management in SQL Server, because this is a very important topic, especially when it comes to TempDb. 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 millenium 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 64 to 128kb, for the 17th page it grows from 128 to 192kb and so on and on. Nowadays you just shake your head about that fact, but in the last millenium this was a very important design choice. The following picture shows (in a very simplified way) what 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 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
In this blog posting 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:
- SQL Server Quickie #2: Extents
- SQL Server Quickie #3: Allocation Units
- SQL Server Quickie #4: IAM Pages
Thanks for your time,
-Klaus
