So what are Pages & Extents, really?

Chronicles of SQL Server's Storage Internals - Part 2

Previous article opened up with how DB Engine stores your files and I mentioned that .MDF file is made of sequence of Pages.

But, what ARE these pages, really? That's what this one is about :)

If we make a parallel between .MDF file being a Book, what do you do when you want to write (or print) something new? You look for an empty page, of course!

Should it come as a surprise that it's EXACTLY what SQL Server does as well? It organizes it's own book (.MDF file) into sequence of pages! Digital pages!

Whenever you ask DB to store something for you (e.g. by issuing INSERT command), it will go and look for, you guessed it, an empty PAGE to write the data to :) It really is THAT simple!

One interesting thing is that pages in regular books vary in size. As in - they can have different formats (e.g. A4, A5, etc.). That sucks! DBs do it way better!

When it comes to pages in SQL Server, they are ALWAYS 8KBs in size (remember - they are DIGITAL pages). ALWAYS.

That means that whatever you write has to fit in 8KBs; or it gets spanned over multiple pages (which sucks for performance BTW! More about it in future).

That also means that if you want to get most efficiency out of your SELECT queries, you'd try fitting as much as possible into as few as possible 8KB chunks!

Now, since there's usually tons of pages out there, there's another really cool concept called - EXTENTS. Extent is nothing more but a logical grouping of 8 pages. And it turns out that whenever SQL Server wants to ALLOCATE more pages, it will allocate EXTENTS instead (i.e. it will always allocate factor of 8 pages).

It's really just an accounting thing and makes some other things a bit easier. But on a high level - EXTENT is nothing more than a container for 8 pages. And that's it!

Finally, just like in Regular Books - there might be pages of different types. There are Book Covers, there's Table of Contents, Index of Words, List of References, ... and then there's actual data (i.e. text).

Shan't come as a surprise it's the same with DB! Pages can be of different type, simply because they have to store different things inside those 8 kilos. And yes, that's what those GAMs, SGAMs, PFSs and IAMs are! They are just DIFFERENT types of pages!

We'll talk more about different types of pages in one of the future articles ;)

For now, what you need to take-away from this is - DB uses concept of pages to WRITE your data inside of them.

Same is true for reading btw! Want to read something? Find a Page that contains it!

As I said, it really IS that simple :)