Hi Friends,

My today’s blog post is focused on trace flag 1118 changes in SQL Server 2016. Every DBA knows, this trace flag is generally used to allocate dedicated or uniform extent rather than mixed extents for databases to minimize the contention of mixed extent allocation. You can read the same in one of my previous blog post by clicking here.

In SQL Server 2016, there is no need for this trace flag because in SQL Server 2016 this feature (uniform extent allocation for first 8 data pages) comes as the default behavior. Let me show you the same.

First, Let me show you that the default behavior of extent allocation in SQL Server 2016:

Trace Flag 1118

From the above output, you can see that by default SQL Server is using uniform extent allocation for first 8 data pages. If you want to change the uniform extent allocation to mixed page allocation then you can use the below TSQL syntax:

By default MIXED_PAGE_ALLOCATION value is OFF. Now let me show you the mixed page allocation for first 8 data pages:

Trace Flag 1118

From the above image, you can see that first 8 data pages have been allocated from two different mixed extents. Here first pages for these extents are page id 304 and 312. Now if you want to check the mixed page allocation property value for the database then you can use the below TSQL syntax:

Trace Flag 1118

HAPPY LEARNING!

Regards:

Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook