3

In SQL Server 2008-2012, objects (including tables) are first put in a mixed extent. An extent can accommodate up to 8 pages and accommodates more than one type of page (meaning: pages from more than one object). Say ExtentA has pages from objects A(2), B(2) and C(4) - numbers in parentheses indicate how many pages they own within that extent. Object B grows, and my question is: Would SQL Server start kicking out A and C's pages before assigning B a new, uniform extent (assuming it eventually outgrows extentA) OR it just jumps to a new extent given extentA is already full.

This question was moved from Stack Overflow to DBA. Comments on the original question:

Valid question, but why do you ask? Seems to be totally unimportant knowledge. – usr

Hi, Thanks for the reply. I'm actually in a very difficult database optimization situation in which I have to crack down on unnecessary spaces being allocated for numerous tables (1000+ tables). So I'm starting from the basics... I couldn't figure out this question because I had no means of tracking it.. I can see that a new extent is being assigned, but I cannot find out whether the previous extent was fully used... – LearnByReading

  • 1
    If Object B grows it will get allocated 6 more pages in other, different, mixed extents. Then when the ninth page was needed a uniform statement will be allocated. I can't imagine there is much milage in trying to "optimise" this aspect either though. Even if your 1,000 objects were all one page and occupying full extents such that 7/8 pages in each were unused you would only be wasting 55MB – Martin Smith Mar 31 '15 at 18:20
1

It just jumps to a new, uniform extent. There is no mechanism to try to convert the first mixed extent into individual uniform extents.

If I understand correctly, when the mixed extent has no data in it any longer, it will switch that extent to being unused. Then it could be used for either a mixed extent or a uniform extent.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.