SQL Server 2016 – Trace Flag 1118 Changes

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:

USE master
go
create database AllocationTest
go
USE AllocationTest
go
create table xtprince
(
    id int identity(1,1),
    name char(8000)
)
go
insert into xtprince values('SQLGEEKS')
go 16
Select DB_NAME(database_id) as DBName,OBJECT_NAME(object_id) as ObjectName,extent_page_id,page_type_desc
from sys.dm_db_database_page_allocations(DB_ID('AllocationTest'),OBJECT_ID('xtprince'),NULL,NULL,'DETAILED')
go

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:

ALTER DATABASE [Database_Name]
SET MIXED_PAGE_ALLOCATION ON

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

   
USE master
go
create database AllocationTest2
go
ALTER DATABASE AllocationTest2
SET MIXED_PAGE_ALLOCATION ON
go
USE AllocationTest2
go
create table xtprince2
(
    id int identity(1,1),
    name char(8000)
)
go
insert into xtprince2 values('SQLGEEKS')
go 16
Select DB_NAME(database_id) as DBName,OBJECT_NAME(object_id) as ObjectName,extent_page_id,page_type_desc
from sys.dm_db_database_page_allocations(DB_ID('AllocationTest2'),OBJECT_ID('xtprince2'),NULL,NULL,'DETAILED')
go

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:

Use [Master]
GO
SELECT name, is_mixed_page_allocation_on
FROM sys.databases where name='AllocationTest'

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

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.