Discover more from Bitesized Engineering
How is Disk Space being managed using Allocation maps?
Chronicles of SQL Server's Storage Internals - Part 6
This time I wouldn't call it SIMPLE out of the box, but once you grasp it - it really IS simple :)
Namely, picture the following - you have an .MDF file which contains all your data - Tables, Rows, Metadata, etc. Everything inside a single binary .MDF file.
In order to make this manageable, the whole content is split into 8KB chunks called "Pages". And these Pages are then further grouped into logical groups called "Extents" (if you need to refresh the memory - check the previous posts).
So we have a huge file segmented into 8KB chunks and all our data (Tables, Rows, Indexes, etc.) is stored across these pages (just like in Books!).
But here's the thing - how do you keep track of which Pages have something, which ones are free, etc.? You need some way to do the accounting, right?
Turns out that this Accounting job is handled by what is called "Allocation Maps". And there are THREE of them :)
It might sound as much but in a nutshell - it's all about efficiency. You really want your DB to be able to RETRIEVE and STORE data as fast as possible. And in order to do that, having THREE levels of accounting really seems to help.
On a first and highest level you keep track of which Extents are allocated and which ones are free. Simple. Because if you want to WRITE something you can just quickly ask the first accountant to tell you the first free Extent. This accountant is called - GAM (Global Allocation Map).
Once you know which ones are free and which ones are occupied, next thing you want to know is - which ones are used for Mixed purpose and which ones are Uniform (hint: check previous article if you didn't read about Mixed & Uniform extents). This is what SGAM (Shared Global Allocation Map) accountant keeps track of.
Finally, it turns out to be useful to know how much free space is left on each free extent. It's useful because you can quickly decide whether you can write your data down to existing page or you need a completely blank one. This accountant is called PFS (Page Free Space). Strangely, it doesn't have the "Allocation Map" as part of the name, but it's job is nonetheless highly important!
And there you have it. Three Accountants where each one needs to know just ONE thing. And that makes it super-fast to navigate around the Pages & Extents and to figure out where to store your data!
P.S. There's a fourth one as well called "IAM" (Index Allocation Map). But that one is a species on it's own and it's a bit more than accountant. It's more of a private lawyer for each table that deals with all it's dirty business. Hence, IAM will be covered as part of another post!
Thanks for reading Bitesized Engineering! Subscribe for free to receive new posts and support my work.