First published on MSDN on Mar 15, 2016
Configuration of TEMPDB is often critical to scalability and throughput of SQL Server applications. The following link (
https://support.microsoft.com/en-us/kb/2964518
) outlines how to configure SQL Server 2012 and SQL Server 2014 for optimal scalability and performance.
A SQL Server 2016 primary goal was ‘It Just Works.’ Out of the box a customer should not have to engage in such documentation and tuning exercises. Whenever possible SQL Server should just work and provide the intended scalability and performance outcomes.
TEMPDB
In the past, we have recommended customers to turn on trace flags 1117 and 1118 for applications that use tempdb heavily. However, adding these flags as startup parameters had an impact for the entire instance as opposed to just tempdb.
-
-T1117 – When growing a data file grow all files at the same time so they remain the same size, reducing allocation contention points.
-
-T1118 – When doing allocations for user tables always allocate full extents. Reducing contention of mixed extent allocations
In SQL Server 2016, the functionality provided by TF 1117 or 1118 will be
automatically
enabled for tempdb. This means, a customer will
no longer
have to enable these trace flags for a SQL Server 2016 instance.
User Databases
For User Databases, trace flags 1117 and 1118 have been replaced with new extensions in ALTER DATABASE commands. Use the ALTER DATABASE syntax to enable or disable the desired trace flag behavior at a database level.
Trace Flag 1118
-
Trace flag 1118 for user databases is replaced by a new ALTER DATABASE setting – MIXED_PAGE_ALLOCATION.
-
Default value of the MIXED_PAGE_ALLOCATION is OFF meaning allocations in the database will use uniform extents.
-
The setting is opposite in behavior of the trace flag (i.e. TF 1118 OFF and MIXED_PAGE_ALLOCATION ON provide the same behavior and vice-versa).
Syntax
ALTER DATABASE <dbname> SET
MIXED_PAGE_ALLOCATION
{ ON | OFF }
For more information, see
https://msdn.microsoft.com/en-US/library/bb522682.aspx
Example
–Default value is OFF so all allocations in AdventureWorks will use uniform extents. To disable and use mixed extents turn the setting to on.
ALTER DATABASE AdventureWorks SET
MIXED_PAGE_ALLOCATION
ON;
Catalog changes
A new column is_mixed_page_allocation_on is added to DMV sys.databases that shows which allocation type (uniform or mixed) is being used. For more information see,
https://msdn.microsoft.com/en-us/library/ms178534.aspx
Trace Flag 1117
-
Trace flag 1117 for user databases is replaced by a new ALTER DATABASE setting at the FILEGROUP level.
-
Default value is to grow a single file – AUTOGROW_SINGLE_FILE (which is same as the trace flag not being enabled).
-
This setting is at the file group level (not the entire database level).
-
For a database that contains many files, the AUTOGROW_ALL_FILES setting has to be to enabled for each filegroup.
Syntax
ALTER DATABASE <dbname> MODIFY FILEGROUP <filegroup> { AUTOGROW_ALL_FILES | AUTOGROW_SINGLE_FILE }
For more information see
https://msdn.microsoft.com/en-us/library/bb522469.aspx
Example
–Default value is AUTOGROW_SINGLE_FILE for all files in all filegroups. To enable growth for all files in a file group in AdventureWorks run the following ALTER DATABASE statement.
ALTER DATABASE AdventureWorks MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES;
Catalog changes
A new column is_autogrow_all_files is added to DMV sys.filegroups that shows which growth setting is being used. For more information see,
https://msdn.microsoft.com/en-us/library/ms187782.aspx
Summary
Use this table for guidance on configuring the settings described in this blog for SQL Server 2016.
|
Database
|
TF 1117
|
TF 1118
|
|
tempdb
|
Not required (default)
|
Not required (default)
|
|
user databases
|
Default behavior will grow single file. Use
ALTER DATABASE <dbname> MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
to grow all files in the filegroup.
|
Not required (default). Use
ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION ON
to go back to using mixed extents
|
|
Other system databases
(master, model, msdb)
|
-NA-
|
Allocations use mixed page extents, cannot be changed.
|
Ajay Jagannathan
(
@ajayMSFT
)
Principal Program Manager
