Changing Page allocation mechanism at database level? MIXED_PAGE_ALLOCATION:

Question

Changing Page allocation mechanism at database level? MIXED_PAGE_ALLOCATION:

VoldemarG

Hall of Fame

Points: 3883
More actions
August 13, 2021 at 5:27 am

#3919475
I have read several publications on the subject of this new in sql 2016 Db Config Option
Found this most illustrative:
https://sqlhints.com/tag/mixed_page_allocation/
It is one of the very few that is available on the subject so far.
1. My main question is: How do I know on which user Db it would be most beneficial to turn it on? (see Alter Database.... below)
  and HOW exactly should we expect such modification to be beneficial, and how such benefit can be measured?
  2) Could there be any serious negative ramification from such action?
--Change MyUserDb page allocation setting
--to allocate first 8 pages from mixed extent
ALTER DATABASE MyUserDb
SET MIXED_PAGE_ALLOCATION ON

THANK YOU!

Questions of the new Era are more like TO SET or NOT TO SET
rather than an outdated 'to be or not to be?' 🙂
Likes to play Chess

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ant-Green SSC Guru Points: 113509 More actions · Answer 1

Well if you manage any SQL2014 or below instances and you have enabled T1118, you have essentially set the value to OFF for every database on that instance.

Now that was for best practise for TempDB and alike, but given that many did have the traceflag enabled, it would seems beneficial to keep the setting switched to OFF going forward, so that the same functionality from older versions is witnessed into the newer versions.

It was all about reducing contention for the GAM/SGAM/PFS pages.

Just reading the top line in Paul Randal's blog (https://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/) , every server in the world should have it enabled and it is the default now in 2016, so based on that it would be one to ensure that from 2016 onwards, the value is always OFF

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

it is 2016, and the default is OFF, as I mentioned...

Likes to play Chess

Ant-Green SSC Guru Points: 113509 More actions · Answer 3

Yes I get that.

But have you ever administered a 2014 or below instance? If yes, then best practise was to put T1118 on.

So as it was best practise in the earlier releases, and it took a while for Microsoft to add it to the product by default, and given what Paul said on his blog post, then leave it as OFF