Do you remember new capabilities shipped with SQL Server 2016 CTP2 about tempdb? In this blog post, I talked about well-known trace flags 1117 and 1118 that become useless for tempdb because their related effects are automatically applied to tempdb.
Another interesting news that comes from the new SQL Server 2016 RC0 is the use of new database-scoped parameters AUTOGROW_SINGLE_FILE / AUTOGROW_ALL_FILES as well as MIXED_PAGE_ALLOCATION that will replace respectively the effects of the 1117 and 1118 trace flags for a particular database. This is a very great news because we will benefit of a more granular configuration regarding our specific workload against one particular database.
Let’s create this simple database and let’s enable the AUTOGROW_ALL_FILES on the primary filegroup
CREATE DATABASE [DB_2016] ON PRIMARY ( NAME = N'DB_2016', FILENAME = N'E:\SQLSERVER\SQL16\DB_2016.mdf' , SIZE = 524288KB , FILEGROWTH = 65536KB ), ( NAME = N'DB_20162', FILENAME = N'E:\SQLSERVER\SQL16\DB_20162.ndf' , SIZE = 524288KB , FILEGROWTH = 65536KB ), ( NAME = N'DB_20163', FILENAME = N'E:\SQLSERVER\SQL16\DB_20163.ndf' , SIZE = 524288KB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'DB_2016_log', FILENAME = N'F:\SQLSERVER\SQL16\DB_2016_log.ldf' , SIZE = 524288KB , FILEGROWTH = 65536KB ) GO ALTER DATABASE [DB_2016] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES; GO
The sys.filegroups system view has a new is_autogrow_files column to check if all files in the same filegroup will grow at the same time
USE [DB_2016]; GO SELECT DB_NAME() AS [db_name], mf.name AS logical_name, fg.name as [filegroup_name], fg.is_autogrow_all_files FROM sys.database_files AS mf JOIN sys.filegroups AS fg ON mf.data_space_id = fg.data_space_id GO
Let’s validate this new option with the following pretty simple test:
CREATE TABLE dbo.test_2016 ( id INT IDENTITY, col1 CHAR(8000) DEFAULT 'T' ); GO INSERT INTO dbo.test_2016 DEFAULT VALUES; GO 210000
File sizes have increased as expected by refering to the following query output:
SELECT name AS logical_name, size / 128 AS size_mb, CAST(FILEPROPERTY(name, 'SpaceUsed') * 100. / size AS DECIMAL(5, 2)) AS [%_free] FROM sys.database_files WHERE type_desc = 'ROWS'
All files seem to have grown with the same target size at the same time according to events found in the default trace.
DECLARE @path NVARCHAR(260); SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc' FROM sys.traces WHERE is_default = 1; SELECT DatabaseName, [FileName], SPID, Duration, StartTime, EndTime, FileType = CASE EventClass WHEN 92 THEN 'Data' WHEN 93 THEN 'Log' END FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass = 92 AND DatabaseName = 'DB_2016' ORDER BY StartTime DESC;
In the next scenario, let’s test the effect of the second interesting MIXED_PAGE_ALLOCATION option. Let’s say we want to disable the allocation of mixed extents (default behavior) by using the following command:
ALTER DATABASE [DB_2016] SET MIXED_PAGE_ALLOCATION OFF; GO
As the previous database-scoped option we are able to control which databases are candidate for mixed or uniform allocations by default. This information is directly accessible from the sys.databases DMV as shown below:
SELECT name, is_mixed_page_allocation_on FROM sys.databases
You may notice that we are now able to confirm that mixed extent allocation is disabled by default for our user database and tempdb as well (without enabling the trace flag 1118).
Let’s perform the quick test of verifying if uniform extend is the default behavior for the DB_2016 database (my database is in SIMPLe recovery model here)
CHECKPOINT; CREATE TABLE dbo.test_20161 ( id INT IDENTITY, col1 CHAR(8000) DEFAULT 'T' ); GO INSERT INTO dbo.test_20161 DEFAULT VALUES; GO 1
Let’s verify the context of the DB_2016 database transaction log. We may notice the related record that confirms a new uniform extent is allocated of the concerned dbo.test_20161 table as shown below:
We may also double check by using the sys.dm_db_database_page_allocations DMF against the same table as following:
SELECT OBJECT_NAME(object_id) AS table_name, index_id, [partition_id], extent_page_id, allocated_page_page_id, page_type_desc as page_type FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.test_20161'), 0, NULL, 'DETAILED')
Stay tuned! In the next blog we’ll see other interesting database-scoped options provided with the future release of SQL Server 2016.