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
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
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'
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
