Unlearn 2 Trace Flag which would not work with SQL Server 2016

Trace flag are switches in SQL Server, which would change the behavior of the SQL Engine. In developer terms they are like “if” clauses in the SQL Server code. If a trace flag is enabled, then certain behavior would change. One of the common trace flag which would be known to almost all DBAs and developers is trace flag 1222, which is used to print the deadlock graph in XML format into a SQL Server ERRORLOG file. So, you can imagine SQL Server code like this.

IF (trace_1222 is ON)
{
print graph in ERRORLOG;
}

Now, since you know the basics – you should know that there are many trace flags available in SQL Server product and no one remembers all of them. There must be some internal trace flags also which are not documented by Microsoft.
If you ask from any DBA about tempdb trace flag, the answer would be 1118. Just to explain what it does – it forces uniform extent allocations instead of mixed page allocations. This trace flag is documented by Microsoft under Knowledge base article 328551. The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points.
Another trace flag for tempdb best practices is 1117. Once the trace flag is enabled, it would cause SQL Server to grow all data files in a filegroup if any file grows. The problem, which we feel, with this trace flag is that it affects all databases on the instance. That’s why people normally avoid it.
Coming in SQL Server 2016, there is no need to trace flags. Both behaviors for tempdb are turned-on, out of the box. Which means, as soon as SQL Server 2016 is installed, it would behave the same way as earlier version used to with the trace flag.

Replacement of 1117

Instead of trace flag, we can use ALTER command to modify the kind of growth we are looking for. By default, tempdb database is set to grow all files in the primary file group at the same time. It can be confirmed by below query.

USE tempdb
GO
SELECT  name 
       ,is_autogrow_all_files
FROM   sys.filegroups
GO

Would you want to change this behavior? Can you can use ALTER DATABASE command as shown below.

ALTER DATABASE TempDB MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE
GO

No, it is not allowed and there is an error message.

Msg 5058, Level 16, State 12, Line 3
Option 'AUTOGROW_SINGLE_FILE' cannot be set in database 'TempDB'.

Replacement of 1118

There is no need to use trace flag 1118 as mixed allocation is a default allocation method for tempDB in SQL Server 2016. Here is the command to confirm the same.

SELECT  name
       ,is_mixed_page_allocation_on
FROM sys.databases
WHERE name = 'tempdb'
GO

You might think that there would be a command to change it. Yes, there is an extension of ALTER DATABASE as below:

ALTER DATABASE TempDB SET MIXED_PAGE_ALLOCATION OFF
GO

Well, if you run the command; it would fail

Msg 5058, Level 16, State 9, Line 7
Option ‘MIXED_PAGE_ALLOCATION’ cannot be set in database ‘TempDB’.

This means that we can’t change the default behavior for tempdb database with respect to allocation and growth.
If you run the queries and you get an error like below

Msg 207, Level 16, State 1, Line 1
Invalid column name ‘is_mixed_page_allocation_on’.

Msg 207, Level 16, State 1, Line 2
Invalid column name ‘is_autogrow_all_files’.

It means you are running this query on version lower than SQL Server 2016.
In summary, SQL Server 2016 has features which can enable few things which might cause performance problems later. The real good thing is that they are enabled by default.