Paul Randal is a SQL Server legend with loads of informative articles. But when I was a baby DBA first reading Inside the Storage Engine, I got a little stuck. It took many passes before, eventually, finally, it clicked. I wish I had a lightweight introduction, so in the practice of paying it forward…
Here’s the starting point: sometimes it’s easier to manage lots of small things (say, the 1s and 0s of data) by grouping them into larger things. It’s the same reason you don’t buy rice by the grain.
For SQL Server, data is logically grouped into 8KB Pages, which are themselves grouped into Extents (an Extent being 8 continuous pages).
Within a file, you need to know where the data is, and that’s why there are mapping pages!
SQL Server uses a combination of maps to describe page usage. The GAM page shows which extents are available (thus it’s 0 for allocated, 1 for open/unused). The SGAM exists because pages in an extent might be used for multiple tables (a mixed extent), or reserved for a single table. The SGAM marks which mixed extents can have a page allocated.
Most mapping pages are data about the space within the file, and are repeated in fixed locations for the area they cover. For example, the GAM page, since it covers about 4GB, is also repeated every 4GB.
Mapping pages for tables, called IAMs, need different treatment however. Think about it – Microsoft doesn’t know ahead of time what tables you’ll have, and can’t have IAMs in fixed locations. So the IAMs contain info about what they cover and where the next IAM is, which allows them to be placed anywhere in the file.
And there’s still more to learn, like those pesky PFS pages! I’m not going to write about them though; Paul already did. So go! Continue your learning journey and profit from your hard-earned knowledge!
