Tested TDE with Instant File Initialization on SQL Server databases

Instant File Initialization (IFI) enabled  on  SQL Server, for data files only, leads to faster  execution on  certain file operations, as  it bypasses the zeroing procedure while reclaiming disk space. Instead, disk content is overwritten as new data is written to the files.

But do these benefits continue  when Transparent Data Encryption (TDE) is enabled ?   

If you want to check the Instant File Initialization status of the SQL Server  with On-Demand Instant File Initialization status with sys.dm_server_services

There are certain circumstances where enabling TDE disables the usage of Instant File Initialization  

1) Auto-growth -    If TDE is enabled on a database  and IFI is turned on   and there is auto-growth  , IFI will be disabled for the mdf files. This could lead to unpredictable performance issues , particuarly if a 10% growth rate is set at the file growth level i.e if the file is large and a 10% growth request occurs - without zeroing - there may be a noticeable drop in service levels during the period of the auto-growth

2) Create new file in a database with TDE enabled – if IFI is enabled and TDE is enabled and a new db file is created , this will bypass the IFI ability to block disable zeroing out on the data file. i.e the new data file will be zeroed out

Example:

USE [master]
GO
ALTER DATABASE [TDE_test] ADD FILE ( NAME = N'TDE_idx3', FILENAME = N'E:\db_path\TDE_idx3.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [PRIMARY]
GO

Zeroing completed on E:\db_path\TDE_idx3.ndf (elapsed = 11 ms)

Note: this message is from output using trace flags detailed below

Trace Flag 3004

Instant file initialization enables data files (not log files) to skip the zeroing out steps in database CREATE. Not so obvious on a small database, but on larger databases such as 2 TB this saves a significant time.

The trace flag 3004 enables information generation. To view the information ,I enable the trace flag 3605. If the service account has Instant File initialization enabled , the zeroing out process is skipped , but if it’s not enabled – you’ll see a zeroing references in the out

Trace Flag 3605

In conjunction with some trace flags , such as 3004 , enabling trace flag 3605 sends information to the error logs.

The syntax is :

DBCC TRACEON (3004, 3605, -1)

DBCC TRACEOFF (3004, 3605, -1)
These are undocumented traces and remain unsupported by Microsoft.

The way I utilise the trace flags is to place the DBCC trace flags before and after the statements.The messages are redirected to the SQL Server log files

DBCC TRACEON (3004, 3605, -1)
create database TDE_test2;
DBCC TRACEOFF (3004, 3605, -1)

xp_readerrorlog

SQL Server faster restores with instant file initialisation