SQL Server 2016 CTP2 : first thoughts about tempdb database

In my first blog about SQL Server 2016, I noticed during the SQL Server installation process that we are able to configure the number of files for tempdb. This is surely a great improvement but at this point, you may think that the configuration of the tempdb database is finished but in fact you’re wrong.

Let me say why. First let’s have a look at the tempdb files configuration after installing SQL Server.

use tempdb;
go
 
select
       name,
       physical_name,
       size * 8 as siz_kb,
       case is_percent_growth
             when 0 then concat(growth * 8, 'KB')
             else concat(cast(growth as decimal(5, 2)), '%')
       end growth
from sys.database_files;
go

On my virtual environment, the installation process has detected 4 VCPUs and it will automatically assign 4 files for tempdb in my case. If we take a look at the BOL, we can see the maximum number of files that can be assigned by the installer is 8. At this point, we may think this is a very interesting improvement because I faced some performance issues caused by a misconfigured tempdb database during my career. However let’s take a look at the default parameters set by the installer: first, all data files are configured with a size of 8MB as well as the growth value with 10%. Next, let’s have a look at the default path … Ok, it seems that we still have extra steps to finalize the configuration of the tempdb database and for the moment, I’m not sure we can push in the trash our post-install script for tempdb. Having some additional parameters from the installer (GUI and script) to configure tempdb may be a good idea, so I guess we should keep an eye on this configuration area for the next CTP release.

But that’s not all, we also have others good news in this topic: trace flags 1117 (grow all files in a filegroup equally) and 1118 (full extent only) are no longer needed for tempdb database. This is a good news because implementing the trace flag 1117 impacts all databases on the server.

Let me show you with some tests:

checkpoint;
go

create table #t_extent_uniform
(
       id int default 1,
       col1 varchar(50) default 'T'
);
go

insert #t_extent_uniform default values;
go

Next. Let’s take a look at the transaction log content by using the undocumented function sys.fn_db_log(). (Note that there are several ways to check allocation. Using sys.fn_db_log() function is one of them).

select
       [Current LSN],
       Operation,
       Context,
       AllocUnitName,
       Description
from sys.fn_dblog(null, null);
go

Ok, after inserting my record, I can confirm that SQL Server has allocated a uniform extent for my object.

Let’s continue by checking if SQL Server will grow uniformly the tempdb data files. First I recreated a temporary table with a sufficient row size to fill up quickly a data page.

create table #t_extent_uniform
(
       id int default 1,
       col1 char(8000) default 'T'
);
go

We will check up the growing and filling of the tempdb data files by using the following script:

use tempdb;
go
 
select
       name,
       physical_name,
       size / 128 as siz_mb,
       case is_percent_growth
             when 0 then concat(growth * 8, 'KB')
             else concat(cast(growth as decimal(5, 2)), '%')
       end growth,
       cast(FILEPROPERTY(name, 'SpaceUsed') * 100. / size as decimal(15, 2)) as space_used_percent
from sys.database_files
where type_desc = 'ROWS'
go

So let’s perform a basic test by inserting a bunch of data to raise a growth of the tempdb data files:
– Here the situation before inserting the data:

– Before raising the growing of the tempdb data files:
At this point, all the data files are filled up by SQL Server as expected:

– And after several insertions and grow of the tempdb data files:

We performed basic tests here and we will have probably to take into account other real scenarios but for the moment, let’s savour these good news. But just to clarify, this improvement is not magic and we will still face some concurrency issues with workloads that use heavily the tempdb database. However,  introducing this new configuration area directly from the installer may encourage DBAs to be aware of the tempdb database.  Just note that it still remains some other interesting mechanisms in order to improve concurrency of tempdb as caching mechanism but it often requires reviewing the T-SQL code from the application side (In my opinion, this is probably the most important improvement that I have ever seen on the tempdb topic).

I’m confident that we’ll get other good news, so stay connected!

By David Barbarin