SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Providing More Feedback on My New Book: Defining Extents

As some of you may know, I am writing a new book called High Performance Index Maintenance. As an experiment within the SQL Server community, I have been asking for DBAs to provide feedback on the books contents.

In my first blog about this topic, I asked people to provide me feedback on the book’s outline. I got a total of 7 people who reviewed my outline and provided feedback. I found this very useful, and the book will be better because of this feedback.

I want to continue this experiment, by occasionally blogging about what I am writing about in the book, and getting your feedback on it.

For example, as DBAs, we all know what an extent is, or do we? As part of an introductory chapter I am writing for the book, I wanted to give a short, succinct definition of what an extent is.

You would think that this would be easy to do, but I did some digging around (because I like to verify my facts) and got two different answers. For example, the SQL Server 2008 Books Online says that an extent is “a collection of eight physically contiguous pages. Then I went to a well-known reference book on SQL Server internals and found this definition: “An extent is made up of eight logically contiguous pages.”

OK, so which is it? Is an extent a collection of eight physical or logical pages?

Tell me what you think, and why. Once I get your feedback, then I’ll share with you what I think the definition should be.



Share this post :


Posted by swjohnson on 19 February 2009


I always understood the extent to be 8 physical contiguous pages (8x8K pages).  An extent can be uniform (all pages of the same object owner) or mixed (a combination of page and index objects).  Most of the time the extent starts off as mixed but when enough of the same type become available, SQL switches them around so that the extent is uniform.  

Posted by Steve Jones on 20 February 2009

I too have always assumed 8 physical since that's how things are allocated. I can't imagine that an allocation takes place with disconnected pages at a time, but it's possible. I'll ping someone.

Posted by David Reed on 20 February 2009

Brad, if you're describing physical storage on disk, it's 8 contiguous physical pages. If memory serves, I think you're talking about the language in Kalen's book where she's describing the difference between mixed and uniform extents, right? I'll have to find my copy and see if there's any errata on that @ MSPress, but BOL is correct. I've never met anybody who cares or talks about "logical extents" since an extent is exclusively a storage concept and after it comes off disk, it's just pages in the buffer pool. If you want to plug in directly with somebody in engine who's read that code recently, just shoot me an email and I'll find you an engineer. HTH!

Posted by Brad M. McGehee on 25 February 2009

I haven't gotten a lot of feedback on the question, but here is my take on why two different sources give different answers as to whether or not an extent is physical or logical. From a SQL Server point of view, the creation of an extent does create eight physical contiguous 8K pages. But why the other resource said that an extent is eight logical 8K pages may be because, at the OS and the disk level, the eight 8K pages may or may not be physically contiguous. So, if you look at an extent from the perspective of the disk level, then you might want to (assuming you want to be 100% technically accurate), say that an extent is eight logical contiguous 8K pages. This is speculation on my part, as I don't know the real reason the other source used this definition. But from a DBA's and SQL Server perspective, an extent is eight physically contiguous 8K pages.

Leave a Comment

Please register or log in to leave a comment.