4

Today I was troubleshooting a problem on TempDB on SQL Server 2016 SP2 using extended events and added the mixed_extent_allocation and transaction_log event to my trace.

I expected mixed_extent_allocation wouldn't show up in my results because following query returned 0 as result:

select is_mixed_page_allocation_on  
from sys.databases 
where database_id=2

But to my suprise this event showed up several times. This was preceded by a transaction_log event with SGAM as context and operation LOP_SET_BITS.

This made me curious and I checked the content of the first SGAM page of TempdB:

DBCC TRACEON(3604)
dbcc page(tempdb,1,3,3)
DBCC TRACEOff(3604)

This is a snippet of the result:

╔═════════════════════════════════════════════╗
║ (1:0)        - (1:176)      = NOT ALLOCATED ║
║ (1:184)      -              =     ALLOCATED ║
║ (1:192)      -              = NOT ALLOCATED ║
║ (1:200)      - (1:208)      =     ALLOCATED ║
║ (1:216)      - (1:256)      = NOT ALLOCATED ║
║ (1:264)      -              =     ALLOCATED ║
║ (1:272)      -              = NOT ALLOCATED ║
║ (1:280)      -              =     ALLOCATED ║
║ (1:288)      - (1:296)      = NOT ALLOCATED ║
║ (1:304)      -              =     ALLOCATED ║
║ (1:312)      -              = NOT ALLOCATED ║
║ (1:320)      -              =     ALLOCATED ║
║ (1:328)      - (1:336)      = NOT ALLOCATED ║
║ (1:344)      -              =     ALLOCATED ║
║ (1:352)      - (1:65528)    = NOT ALLOCATED ║
╚═════════════════════════════════════════════╝

This made me conclude that mixed extents are still used. I thought SQL Server 2016 only uses uniform extents (except for master, msdb and model). Or is my conclusion wrong?

Why am I still seeing the mixed_extent_allocation event popping up?

2

According to this post, IAM pages are still allocated from mixed extents, even in SQL Server 2016. So this will be the mixed_extent_allocation I saw in my trace.

The blog post by Paul Randal provides some more internals explanation. Thanks to Scott Hodgin for pointing me to the article.

I did soms more research about the content of de pages allocated in the first SGAM page and found out that SQL Server also uses mixed extents for system tables (object_id lower than 100).

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.