Bitesized Engineering

Share this post

So what are Pages & Extents, really?

www.bitesizedengineering.com

Discover more from Bitesized Engineering

Engineering deep-dives for People in a hurry.
Over 1,000 subscribers
Continue reading
Sign in

So what are Pages & Extents, really?

Chronicles of SQL Server's Storage Internals - Part 2

Mihailo Joksimovic
Sep 6, 2022
Share this post

So what are Pages & Extents, really?

www.bitesizedengineering.com
Share

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 :)

Thanks for reading Bitesized Engineering! Subscribe for free to receive new posts and support my work.

Share this post

So what are Pages & Extents, really?

www.bitesizedengineering.com
Share
Comments
Top
New
Community

No posts

Ready for more?

© 2023 Mihailo Joksimovic
Privacy ∙ Terms ∙ Collection notice
Start WritingGet the app
Substack is the home for great writing