Infrastructure at your Service

David Barbarin

SQL Server 2016: new database-scoped configuration parameters

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

 

blog 84 - 1 - AUTOGROW_ALL_FILES

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'

 

blog 84 - 2 - new file sizes

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;

 

blog 84 - 3 - autogrow event

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

 

blog 84 - 4 - mixed extent info 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:

blog 84 - 5 - fn_dblog

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')

 

blog 84 - 6 - DMF

Stay tuned! In the next blog we’ll see other interesting database-scoped options provided with the future release of SQL Server 2016.

 

 

 

 

 

Leave a Reply


+ 8 = seventeen

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader