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 2014 and 2012 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
One of these changes is TEMPDB always assumes -T1117 and -T1118 behavior.
In summary, SQL Server 2016 no longer requires one to turn on TF 1117 or 1118.
User Database
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
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
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
'It Just Runs Faster' - Out of the box SQL Server 2016 enables the -T1117 and -T1118 behavior for TEMPDB providing better scalability and performance.
Ajay Jagannathan - SQL Server Principal Program Manager
Bob Dorr - Principal SQL Server Escalation Engineer
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.