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

  • Comments posted to this topic are about the item An In-Depth Look at MIXED_PAGE_ALLOCATION and Changes in Page Allocation

  • 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 11 months ago by  Uwe Ricken.

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

  • 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?

     

     

  • 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.

    God is real, unless declared integer.

  • 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 11 months ago by  Jeff_C.
  • 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

  • This was removed by the editor as SPAM

  • Parthprajapati wrote:

    Appreciate the feedback! While MIXED extents have limitations, they can still offer performance &amp; storage benefits in specific cases like TEMPDB management. I'm open to further discussion &amp; 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...

    God is real, unless declared integer.

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

  • definitely, oh well...

    there are too many chitters lately.

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

  • 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)

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

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

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

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