Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Trace Flags 1118 and 1117 for Page Allocations


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

Attend a SQL Server Conference for FREE >> click to learn more


Problem

SQL Server 2016 provides several new features along with modification and simplification of existing features. There are a few trace flag changes as well, which is important to know since it might help in performance troubleshooting and improvement of the overall database system. In this tip we are going to see these Trace Flag changes and the default behavior in SQL Server 2016.

Solution

Before we start with the specific Trace Flags, let me first give a brief introduction to SQL Server pages and extent allocations.

  • Page: A page is the most basic element of storage in SQL Server. The size of a page is 8KB.
  • Extent: An extent is a set of 8KB contiguous data pages, so it is 8 * 8KB = 64KB in size.

There are two types of extents in SQL Server:

  • Uniform Extent: An extent where all 8 pages are reserved or filled with one table's data.
  • Mixed Extent: An extent where 8 pages are shared by multiple objects.

Now that we have that covered, let's look at how the trace flags work.

SQL Server Trace Flag 1118

Up to SQL Server 2014 we had Trace Flag 1118 which is used to allocate a Uniform Extent instead of Mixed Extents to minimize contention in extent allocation. If this trace flag is enabled, then the first 8 data pages for tables were also Uniform Extents rather than Mixed Extents.

In SQL Server 2016, this uniform extent allocation is the default behavior and we can change this behavior if required by using an ALTER DATABASE command, however we cannot change the behavior for the TempDB database.  We can check the default behavior using the sys.databases catalog view which includes an additional column: is_mixed_page_allocation_on.

SQL Server Mixed Page Allocations per Database

As we can see, is_mixed_page_allocation_on is set to 1 for master, model and msdb which means these database tables will get the first 8 pages from the Mixed Extent and subsequent pages from the Uniform Extent. We can also see that is_mixed_page_allocation_on is set to 0 for TempDB and user defined databases which means all the pages for the table are from a Uniform Extent.

Now let's explore this default behavior with an example:

CREATE DATABASE mixed_page_allocation_test
GO
USE mixed_page_allocation_test GO
CREATE TABLE Test ( testallocation char(8000) ) GO

A page in SQL Server is 8 KB in size (i.e. 8192 bytes) with approximately 8060 bytes available for data due and the rest for the page header and row offset.  Here is a formula:

Page (8 KB/8192 bytes) = Page header (96 bytes) + Actual data (Whatever bytes) + Row offset (2 bytes per row)

As our Test table contains a column of char(8000) data type, we should have one data page per row, so let's add some sample data into the table and review the behavior. We will be using the DMV sys.dm_db_database_page_allocations (or we can use DBCC IND) to view the extent allocation.

INSERT INTO Test VALUES('SQL2016')
GO 20
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('mixed_page_allocation_test'), OBJECT_ID('test'),NULL,NULL,'DETAILED') go

As we can see below, SQL Server 2016 is allocating uniform extents by default and once an extent is completely allocated another uniform extent is allocated.

SQL Server Page Allocations for the Test table

When we enabled Trace Flag 1118 in SQL Server 2014, the setting is applied to the SQL Server instance (i.e. for all databases).  Now with SQL Server 2016 we can control the behavior at the database level also.  With SQL Server 2016 for TempDB page allocations, there will always be Uniform Extents without using Mixed Extents.

Now we will disable the behavior with the ALTER DATABASE command:

CREATE DATABASE mixed_page_allocation_test_1
GO
USE mixed_page_allocation_test_1 GO
CREATE TABLE test_1 ( testallocation_1 char(8000) ) GO
-- command to set mixed_page_allocation to ON ALTER DATABASE mixed_page_allocation_test_1 SET MIXED_PAGE_ALLOCATION ON

Configure a database with MIXED_PAGE_ALLOCATION ON

Now insert the same test data into the table as we did above to see the behavior for page allocations.

SELECT DB_NAME(database_id) as DBName,OBJECT_NAME(object_id) as ObjectName,
extent_page_id,page_type_desc,is_mixed_page_allocation 
FROM sys.dm_db_database_page_allocations(DB_ID('mixed_page_allocation_test_1'),
OBJECT_ID('dbo.test_1'),NULL,NULL,'DETAILED')
GO

We can see the first 8 data pages have been allocated from different extents. Here the pages for these extents are page_id 32, 40, 72, 80 and 88.

Review the SQL Server Page Allocations for the Test_1 table

As previously stated, TempDB by default uses mixed page allocation set to 0 and we cannot turn this Mixed_Page_Allocation On for Tempdb. If we try as shown below, we will get the following error:

Error message when trying to change the page allocations for TempDB

SQL Server Trace Flag 1117

You might have multiple TempDB data files and SQL Server uses them in a round robin fashion, unless they are not the same size. If one file has to grow and the others do not grow with it, SQL Server will tend to use the larger file for most operations. So up to SQL Server 2014, we had to size all TempDB data files the same, set the same autogrowth for each as a fixed size and implement trace flag T1117 by which SQL Server forces all of the database data files to grow when any individual file needs to grow.  For user defined databases we can also enable the files to equally grow. 

With SQL Server 2016 this setting is automatically enabled which means SQL Server will try to grow all of the files, but will only grow the ones that can.  Here are some sample scripts:

--ALTER DATABASE <dbname> MODIFY FILEGROUP <filegroup> { AUTOGROW_ALL_FILES | AUTOGROW_SINGLE_FILE }
-- to autogrow all files in the filegroup ALTER DATABASE Adventureworks2012 MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES GO
--to autogrow single file in the filegroup ALTER DATABASE Adventureworks2012 MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE GO

An important point to note is that the default value is AUTOGROW_SINGLE_FILE which is the same as when the Trace Flag is disabled.  Also keep in mind this setting is set at the file group level not at the database level.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools