SQL Server 2016 – Introducing AutoGrow and Mixed_Page_Allocations Options – TraceFlags

I have been playing around with SQL Server 2016 for a while now and I have seemed to have learnt a number of interesting options when playing around with the new version. One of the readers after reading – SQL Server 2016 – Enhancements with TempDB asked me if the option of TraceFlags of 1117 and 1118 not available when working with user defined normal databases. This was a valid and appropriate question for sure. I was not sure where to start. Let us see traceflags related to AutoGrow and Mixed_Page_Allocations.

I thought this is not the only time someone asked an intelligent question. I was sure there is something that Microsoft folks must have already thought. And going through the documentation of SQL Server 2016, I was pleasantly surprised because that tempted me to write this blog finally.

There is nothing rocket science about this. Trace Flag 1118 is used for getting a uniform extent.

ALTER DATABASE Adventureworks2016 SET MIXED_PAGE_ALLOCATION OFF
GO

As you can see, this option allows to set the default setting for how pages will be allocated. This is OFF by default and uniform extents are allocated by default when working with SQL Server 2016. I personally found this really interesting addition to the ALTER command working with SQL Server.

Now moving to Trace Flag 1117, this is about how the growth happens to data files inside a filegroup. It is recommended to have the same data file sizes within a given Filegroup. Also, when the files grow, it is recommended to grow them uniformly. There was no direct way to specify till I stumbled on the documentation.

ALTER DATABASE Adventureworks2016 MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES  
GO
ALTER DATABASE Adventureworks2016 MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE
GO

Another great addition done to the SQL Server 2016’s ALTER command which can easily get unnoticed. As always, Microsoft has gone the extra mile of making sure the defaults are set for the best practices. But there might be situations when you want to deferentially grow the files because of space constraints in the hard disk on which these files are located.

I thought this blog can be used by friends who might potentially get into SQL Server 2016 and are looking for playing around with these options when working with SQL Server.

On a curious note, how many reading this blog have enabled the trace flags of 1117 and 1118 in your environments? Did you ever know you had to enable these on your production boxes? How open are you to enable such trace flags in your environments? Please let me know what you are doing so that we can learn together.

Reference: Pinal Dave (https://blog.sqlauthority.com)