An In-Depth Look at MIXED_PAGE_ALLOCATION and Changes in Page Allocation

Question

An In-Depth Look at MIXED_PAGE_ALLOCATION and Changes in Page Allocation

Parthprajapati

SSC Veteran

Points: 252
More actions
January 10, 2024 at 12:00 am

#4346169

Comments posted to this topic are about the item An In-Depth Look at MIXED_PAGE_ALLOCATION and Changes in Page Allocation
January 10, 2024 at 4:02 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Uwe Ricken Hall of Fame Points: 3169 More actions · Answer 1

I suspect that you wanted to describe something else with your article but not the benefits of MIXED vs. UNIFORM extents.

The default behavior of SQL Server BEFORE SQL 2016 was the allocation of mixed extents.
In order to only use UNIFORM extents, TF 1118 had to be activated and was recommended by Microsoft.
SQL 2016 did NOT changed the behavior. It only made the use of UNIFORM extents a default! In particular, access to TEMPDB could be significantly optimized.
MIXED extents could never contain DATA and INDEX pages. Rather, MIXED in the context means that data pages from different objects (tables) are stored in one extent.
There is no column "mixed_extent_count" in sys.databases and there hasn't been one yet
Can you please prove how a query performance improvement of 30% can only be achieved by using MIXED Extents?
Sorry, but in my opinion that's far-fetched.
The claim that MIXED Extents save “significant” storage is also ridiculous. An object only uses a MIXED extent once until the object occupies at least 8 data pages. UNIFORM Extends are then automatically used.
MIXED Extents will automatically converted to UNIFORM when an INDEX Rebuild happens
Why should the use of MIXED extents be controlled? It doesn't make any sense at all since they are only used once for an object until it uses 8 pages of data. Only in TEMPDB does it happen that new objects are constantly created. This is exactly where MIXED extents would be a performance killer!

My conclusion: MIXED extents have always been counterproductive and will continue to be so. In particular, the system database TEMPDB suffered extremely from the use of TEMPDB (latch contention!). In principle, MIXED extents should not be used - neither for system nor user databases.

This reply was modified 10 months, 2 weeks ago by Uwe Ricken.

Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Thank you for your insights.

Maybe I am confused.

I thought the difference between Mixed and Uniform Extents was that

Uniform: All pages from the same object

Mixed: Pages from different objects

Your definition is

Uniform: Extent contains either data or index pages.

Mixed: Can be data & index pages.

Am I mistaken?

Thomas Franz Hall of Fame Points: 3893 More actions · Answer 3

In my opinion this article was written by ChatGPT or another KI tool with no real knowlegde. Besides the errors mentioned by Uwe Ricken it is not more than a endless repeating text which does not really say anything and has no "red string" / focus / real information.

This reply was modified 10 months, 2 weeks ago by Thomas Franz.

God is real, unless declared integer.

Jeff_C Old Hand Points: 344 More actions · Answer 4

Hi,

As per the MS documentation (The highlighting is mine).

<mixed_page_allocation_option> ::=

Applies to: SQL Server (Starting with SQL Server 2016 (13.x))

Controls whether the database can create initial pages using a mixed extent for the first eight pages of a table or index.

MIXED_PAGE_ALLOCATION { OFF | ON }

OFF

The database always creates initial pages using uniform extents. OFF is the default value.

ON

The database can create initial pages using mixed extents.

This setting is ON for all system databases. The tempdb system database is the only system database that supports OFF.

So what does this mean? If we then take a look a this article by Bob Dorr, we can see that it replaces the requirement for, in previous versions using TF1118. The behaviour change in 2016 also defaults to -TF1118 being applied by setting MIXED_PAGE_ALLOCATION off.

As explained in this article,

SQL Server has two types of extents:

Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.

Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

It is this behaviour which is controlled by the mixed_page_allocation setting.

This reply was modified 10 months, 2 weeks ago by Jeff_C.

david.gugg SSCertifiable Points: 6096 More actions · Answer 5

I thought the same thing as Thomas Franz. This article is clearly written by AI.

Be still, and know that I am God - Psalm 46:10

Thomas Franz Hall of Fame Points: 3893 More actions · Answer 6

Parthprajapati wrote:
Appreciate the feedback! While MIXED extents have limitations, they can still offer performance & storage benefits in specific cases like TEMPDB management. I'm open to further discussion & exploring alternative perspectives.

Okay, how and why? And why, when some of the basic understandings / assumations of MIXED_PAGE_ALLOCATION in the article were wrong?

And please no KI answer...

This reply was modified 10 months, 2 weeks ago by Thomas Franz.

God is real, unless declared integer.

1974lg Old Hand Points: 374 More actions · Answer 7

thank you for the article, but it is really misleading and not providing much useful information.

1974lg Old Hand Points: 374 More actions · Answer 8

definitely, oh well...

there are too many chitters lately.

it would be great to remove this article not to create confusions.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 9

Rule number 1: Claims of efficiency and/or performance MUST be accompanied by demonstrable code to prove it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 10

Defintley looks like this was ChatGpt assisted at the least. Very non specific and lacking use cases.

----------------------------------------------------