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
Best Answer
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.